Re: 'distinct on' and 'order by' conflicts of interest

On Fri, 31 Dec 2004 stephen(at)thunkit(dot)com 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.