Re: Can null values be sorted low?

--- ann hedley <ann.hedley@ed.ac.uk> wrote:
> The problem...
>  From the table below I want to select distinct
> clus_id and the data in
> 3 db columns, even if that data is null.
>
> When there are multiple contigs for a clus_id I want
> to select the row
> where 1st, the most db columns have a value, 2nd the
> total value of the
> db columns is highest.
>
> So for
> LRC00006 I want contig 3,
> LRC00010 I want contig 4, because it has 3 db values
> (even though contig
> 2 would total more)
> LRC00001 I want contig 2, because it would total
> more if the null value
> was treated as zero in the calculation
> and for the rest I want contig 1.
>
> Any hints on writing such a query would be much

If you don't mind using a PostgreSQL-only extension,
"SELECT DISTINCT ON" may do what you want: check the
"SELECT" page in the "SQL Commands" section of the
manual.

You will also want to use "coalesce" to replace null
values, e.g. "select coalesce(db0,0)"

> appreciated.
>
> lumbribase=# select
> clus_id,contig,db0,db1,db4,(db0+db1+db4) as total
> from venn order by clus_id,total DESC;
>  clus_id  | contig | db0  | db1  | db4  | total
> ----------+--------+------+------+------+-------
>  LRC00001 |      1 | 77.4 |      | 63.9 |
>  LRC00001 |      2 | 77.4 |      | 82.7 |
>  LRC00002 |      1 |  325 |  343 |  313 |   981
>  LRC00003 |      1 |      |      |      |
>  LRC00004 |      1 |      |      |      |
>  LRC00005 |      1 |  294 |  294 |  116 |   704
>  LRC00006 |      1 |  100 | 72.8 |      |
>  LRC00006 |      3 |  120 |  122 | 63.9 | 305.9
>  LRC00006 |      2 |  117 |  112 | 58.5 | 287.5
>  LRC00007 |      1 |  178 |      |      |
>  LRC00008 |      1 |      |      |      |
>  LRC00009 |      1 |  416 |      |      |
>  LRC00010 |      2 |  324 |  167 |     |
>  LRC00010 |      4 |  146 |  168 |  172 |   486
>  LRC00010 |      1 |  146 |  166 |  171 |   483
>  LRC00010 |      3 |  145 |  160 |  159 |   464
>  LRC00011 |      1 |  179 |  100 | 95.1 | 374.1
>  LRC00012 |      1 |  639 |  639 |  633 |  1911
>  LRC00012 |      3 |  505 |  509 |  508 |  1522
>  LRC00012 |      2 |  390 |  391 |  392 |  1173
>
>
>
> --
> Ann
>
> "In a world without walls and fences - who needs
> Windows and Gates ?"
>
>       (unknown)
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>



__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail