Skip site navigation (1) Skip section navigation (2)

Re: Can null values be sorted low?

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Can null values be sorted low?
Date: 2005-05-14 15:24:19
Message-ID: 20050514152419.12168.qmail@web20827.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- 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 

Responses

pgsql-novice by date

Next:From: Tom PfeiferDate: 2005-05-15 00:04:14
Subject: [Win2k - Version 8.0.2] - StartupMessage Format Question
Previous:From: Mikko HeiskanenDate: 2005-05-14 14:56:24
Subject: Unable to connect from other machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group