Sorry, the query you provided works in Pg, my mistake :! select atcode from table group by atcode order by max(torder);
johnl -----Original Message----- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 4:35 PM To: Tom Lane Cc: John Liu; [EMAIL PROTECTED] Subject: Re: [GENERAL] select distinct w/order by On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <[EMAIL PROTECTED]> 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 8: explain analyze is your friend
