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: 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? ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
