I don't think PG allows your query -
select distinct atcode from tmp group by atcode order by max(torder);
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
I can't make Tom's query work either -
select atcode from (select distinct on (atcode) atcode, torder from tmp
order by atcode, max(torder)) ss order by torder;
ERROR: Attribute tmp.atcode must be GROUPed or used in an aggregate
function
Thanks for providing the alternative trials.
johnl
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, March 31, 2004 4:35 PM
To: Tom Lane
Cc: John Liu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] select distinct w/order by
On Wed, 31 Mar 2004, Tom Lane wrote:
> "John Liu" <johnl@emrx.com> writes:
> > The original simple SQL -
> > select distinct atcode from TMP order by torder;
>
> This is not "simple", it is "broken SQL with an undefined result".
>
> If DISTINCT merges multiple rows with the same atcode, how are we
> supposed to know which row's value of torder to sort the merged
> row on?
>
> Your other database was no doubt making a random choice and giving
> you a random result ordering in consequence. You need to think harder
> about what behavior you really want.
>
> Once you can define the behavior (ie, just which torder you want to use)
> you can probably implement it with something like
>
> select atcode from
> (select distinct on (atcode) atcode, torder from table
> order by atcode, ???
> ) ss
> order by torder;
>
> where the ??? ordering determines which torder you get in each atcode
group.
> See the SELECT DISTINCT ON example in the SELECT reference page.
I did it like this:
select atcode from table group by atcode order by max(toorder);
Is that equivalent?
