Re: select distinct w/order by - Mailing list pgsql-general

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@megazone.bigpanda.com]
Sent: Wednesday, March 31, 2004 3:35 PM
To: John Liu
Cc: pgsql-general@postgresql.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.