Re: Can null values be sorted low?

OK

If your interested this is my solution to the problem. I'm sure there
are more efficient ways but this way works and that's what's important
at the moment.

While looking up Jeff's tips I discovered the CASE clause and used it to
generate two columns, a count of the dbs and a total across the dbs
avoiding the null values. I could then order these to get the required
row first.

SELECT distinct on (clus_id) clus_id,contig,db0,db1,db4,
 CASE
  WHEN db0 is not null and
       db1 is not null and
       db4 is not null
  THEN 3
  WHEN db0 is not null and
       db1 is not null
  THEN 2
  WHEN db0 is not null and
       db4 is not null
  THEN 2
  WHEN db1 is not null and
       db4 is not null
  THEN 2
 END as db_count,
 CASE
  WHEN db0 is not null and
       db1 is not null and
       db4 is not null
  THEN (db0+db1+db4)
  WHEN db0 is not null and
       db1 is not null
  THEN (db0+db1)
  WHEN db0 is not null and
       db4 is not null
  THEN (db0+db4)
  WHEN db1 is not null and
       db4 is not null
  THEN (db0+db1)
  WHEN db0 is not null
  THEN db0
  WHEN db1 is not null
  THEN db1
  WHEN db4 is not null
  THEN db4
 END as total
from venn order by clus_id,db_count DESC,total DESC;

 clus_id  | contig | db0    | db1  | db4  | db_count | total
----------+--------+---------+------+------+----------+-------
 LRC00001 |      2 | 77.4 |         | 82.7 |          2 | 160.1
 LRC00002 |      1 |  325 |  343 |  313 |          3 |   981
 LRC00003 |      1 |         |         |         |             |
 LRC00004 |      1 |         |         |         |             |
 LRC00005 |      1 |  294 |  294 |  116 |          3 |   704
 LRC00006 |      3 |  120 |  122 | 63.9 |          3 | 305.9
 LRC00007 |      1 |  178 |         |         |             |   178
 LRC00008 |      1 |         |         |         |             |
 LRC00009 |      1 |  416 |         |         |             |   416
 LRC00010 |      4 |  146 |  168 |  172 |          3 |   486
 LRC00011 |      1 |  179 |  100 | 95.1 |          3 | 374.1
 LRC00012 |      1 |  639 |  639 |  633 |          3 |  1911


Jeff Eckermann wrote:

>--- 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>

--
Ann

"In a world without walls and fences - who needs Windows and Gates ?"
                                                           (unknown)