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)