Re: select distinct w/order by

Thanks, scott.marlowe provides similar query. For this simple case the
result is the same. Here's another case, the result is different -

1. In the database allowing 'illegal distinct/w orderby not in the target
list'
select distinct drugname, drugid, encdate from CCMMed where pnum_site
='1913789_MC' order by drugname, encdate, mshdatetime desc;
drugname
drugid encdate

ALLOPURINOL
554 04/24/2000
ALLOPURINOL
554 05/14/2001
ALLOPURINOL
554 06/15/2001
ALLOPURINOL
554 08/20/2001
ALLOPURINOL
554 11/26/2001
ALLOPURINOL
554 05/22/2002
ALLOPURINOL
554 09/23/2002
ALLOPURINOL
554 01/13/2003
ALLOPURINOL
554 05/27/2003
ALLOPURINOL
554 09/29/2003
GLYBURIDE
1742 05/14/2001
GLYBURIDE
1742 06/15/2001
GLYBURIDE
1742 08/20/2001
GLYBURIDE
1742 11/26/2001
GLYBURIDE
1742 05/22/2002
GLYBURIDE
1742 09/23/2002
GLYBURIDE
1742 01/13/2003
GLYBURIDE
1742 05/27/2003
GLYBURIDE
1742 09/29/2003

2. In Pg, use your query group by then order by -
select drugname, drugid, encdate from ccmmed where pnum_site ='1913789_MC'
group by drugname, drugid, encdate order by max(mshdatetime);
drugname | drugid | encdate
-------------+--------+------------
ALLOPURINOL | 554 | 2000-04-24
ALLOPURINOL | 554 | 2001-05-14
GLYBURIDE | 1742 | 2001-05-14
GLYBURIDE | 1742 | 2001-06-15
ALLOPURINOL | 554 | 2001-06-15
ALLOPURINOL | 554 | 2001-08-20
GLYBURIDE | 1742 | 2001-08-20
GLYBURIDE | 1742 | 2001-11-26
ALLOPURINOL | 554 | 2001-11-26
ALLOPURINOL | 554 | 2002-05-22
GLYBURIDE | 1742 | 2002-05-22
GLYBURIDE | 1742 | 2002-09-23
ALLOPURINOL | 554 | 2002-09-23
ALLOPURINOL | 554 | 2003-01-13
GLYBURIDE | 1742 | 2003-01-13
GLYBURIDE | 1742 | 2003-05-27
ALLOPURINOL | 554 | 2003-05-27
ALLOPURINOL | 554 | 2003-09-29
GLYBURIDE | 1742 | 2003-09-29

3. My alternative in Pg for the above case -
select distinct drugname, drugid, encdate from (select drugname, drugid,
encdate, mshdatetime from CCMMed where pnum_site ='1913789_MC' order by
drugname, encdate, mshdatetime desc) t;
drugname | drugid | encdate
-------------+--------+------------
ALLOPURINOL | 554 | 2000-04-24
ALLOPURINOL | 554 | 2001-05-14
ALLOPURINOL | 554 | 2001-06-15
ALLOPURINOL | 554 | 2001-08-20
ALLOPURINOL | 554 | 2001-11-26
ALLOPURINOL | 554 | 2002-05-22
ALLOPURINOL | 554 | 2002-09-23
ALLOPURINOL | 554 | 2003-01-13
ALLOPURINOL | 554 | 2003-05-27
ALLOPURINOL | 554 | 2003-09-29
GLYBURIDE | 1742 | 2001-05-14
GLYBURIDE | 1742 | 2001-06-15
GLYBURIDE | 1742 | 2001-08-20
GLYBURIDE | 1742 | 2001-11-26
GLYBURIDE | 1742 | 2002-05-22
GLYBURIDE | 1742 | 2002-09-23
GLYBURIDE | 1742 | 2003-01-13
GLYBURIDE | 1742 | 2003-05-27
GLYBURIDE | 1742 | 2003-09-29

Note the same alternative approach for the simple query in my first post
email is not working in Pg.

Thanks.
johnl
-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
Sent: Wednesday, March 31, 2004 3:35 PM
To: John Liu
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] select distinct w/order by

On Wed, 31 Mar 2004, John Liu 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"!!)
>
> I tried to rewrite the above simple query in PostgreSQL as - select
distinct
> atcode from (select atcode,torder from TMP order by torder) t;
>
> Can anybody provide a real/general solution to the above practical
problem?
> (Tom?) This causes postgreSQL users too much time and headache.

Is atcode unique or can you assume that the torder values are the same for
different rows of the same atcode?

In general, I think something of the general form:
select atcode from TMP group by atcode order by min(torder);
may actually give results resembling what you want.