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

Re: select distinct w/order by

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: John Liu <johnl(at)emrx(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: select distinct w/order by
Date: 2004-03-31 19:58:15
Message-ID: 20040331195815.8861.qmail@web20812.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
--- John Liu <johnl(at)emrx(dot)com> wrote:
> I know this is an old topic, but it's not easy to
> find a way around it, so
> when we migrate SQL from other database to
> PostgreSQL, it causes a huge
> headache. Here's an extremely simple example -
> 
> The original simple SQL -
> select distinct atcode from TMP order by torder;
> 
> (it'll error out in PostgreSQL, although SQL92
> extension may allow it;
> there's time you just can't do "select distinct
> atcode,torder from TMP order
> by torder"!!)
> 
> My desire result -
>  HGB         
>  HCT        
>  WBC        
>  RBC        
>  MCV       
>  MCH      
>  MCHC        
>  RDW        
>  RDWSD       
>  PLT         
>  DIFF | TYPE 
>  SEGS        
>  LYMPHS      
>  MONOS       
>  EOS         
>  BASOS

What rule are you using to decide that order?  If
there are multiple values of torder for a given value
of atcode, which of those values should be used for
ordering?

"DISTINCT ON", which is a PostgreSQL extension, may do
what you want (depending on your answer to the above
questions).  Look at the "SELECT" page in the docs on
"SQL Commands".

> 
> I tried to rewrite the above simple query in
> PostgreSQL as - select distinct
> atcode from (select atcode,torder from TMP order by
> torder) t;
> 
> But the return results are not what I want -  BASOS 
> DIFF | TYPE  EOS  HCT
> HGB  LYMPHS  MCH  MCHC  MCV  MONOS  PLT  RBC  RDW 
> RDWSD  SEGS  WBC
> 
> Can anybody provide a real/general solution to the
> above practical problem?
> (Tom?) This causes postgreSQL users too much time
> and headache.
> 
> Thanks.
> johnl
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>               
> http://archives.postgresql.org-------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org


__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html

In response to

pgsql-general by date

Next:From: William WhiteDate: 2004-03-31 20:32:48
Subject: Re: Question about rtrees (overleft replacing left in nodes)
Previous:From: Tom LaneDate: 2004-03-31 19:41:43
Subject: Re: select distinct w/order by

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