Re: Can null values be sorted low?

--- ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)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(at)postgresql(dot)org
>

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