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

select distinct w/order by

From: "John Liu" <johnl(at)emrx(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: select distinct w/order by
Date: 2004-03-31 17:50:24
Message-ID: 200403311750.i2VHoScU020278@mail.stihealthcare.com (view raw or flat)
Thread:
Lists: pgsql-general
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




In response to

Responses

pgsql-general by date

Next:From: Mooney, RyanDate: 2004-03-31 17:50:25
Subject: Re: Large DB
Previous:From: William ReeseDate: 2004-03-31 17:49:02
Subject: Problem with foreign keys and locking

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