Can null values be sorted low?

From: ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Can null values be sorted low?
Date: 2005-05-14 10:44:40
Message-ID: 4285D698.6050101@ed.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 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)

Browse pgsql-novice by date

  From Date Subject
Next Message Vitaly Belman 2005-05-14 11:02:17 Re: Fwd: [NOVICE] Autocommit in Postgresql
Previous Message Vyom A 2005-05-14 09:36:05 Re: Starting the Database