Re: select distinct w/order by

Don't know why this is not posted ...

-----Original Message-----
From: John Liu [mailto:johnl@emrx.com]
Sent: Wednesday, March 31, 2004 11:50 AM
To: 'pgsql-general@postgresql.org'
Subject: select distinct w/order by

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

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