On Fri, 31 Dec 2004 [EMAIL PROTECTED] wrote: > It has come up several times on the various postgresql lists that in order > to get around the requirement of DISTINCT ON parameters matching the first > ORDER BY parameters, wrap the distinct query in a new 'order by' query: > > select * from (select distinct on (a) a,b,c from foo order by a) order by c > > however, this will not work when ordering by 'a' will put the wrong record > first, making it choose the wrong record via distinct on. The 'order by > c' superquery no longer has the correct recordset to sort.
I'm not entirely sure from the above which record you want to get from the distinct on. In general for something like the above, in the subselect you order by the distinct on column(s) and then the column(s) which control the which row you want (for example order by a,c). Then you can use the outer order by to change the ordering to no longer use the distinct on columns. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
