Re: Can null values be sorted low? - Mailing list pgsql-novice
From | ann hedley |
---|---|
Subject | Re: Can null values be sorted low? |
Date | |
Msg-id | 42885B7E.2060109@ed.ac.uk Whole thread Raw |
In response to | Re: Can null values be sorted low? (Jeff Eckermann <jeff_eckermann@yahoo.com>) |
List | pgsql-novice |
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)
pgsql-novice by date: