Re: Distinct on a non-sort column

On 11/5/2011 4:11 PM, Tom Lane wrote:
> Cstdenis<lists(at)on-track(dot)ca> writes:
>> If I understand that you are proposing as
>> select * from
>> (select distinct on (user_id) * from stories as s order
>> by user_id) as foo
>> order by date_submitted desc limit 10;
> No, you always need to sort by *more* columns than are listed in
> DISTINCT ON. That's what determines which row is picked in each
> DISTINCT group. Read the SELECT reference page's example of how
> to use DISTINCT ON.
>
> regards, tom lane
>
Thanks. I was missing the obvious. This seems to give the results I need.

It's a shame the query parser isn't able to simply internally process
the query like that -- doing a second sort pass after the distinct
automatically in the case of the sort being on a different column from
distinct (instead of producing an error).