Re: Distinct on a non-sort column

On 11/5/2011 4:11 PM, Tom Lane wrote:
> Cstdenis<lists@on-track.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).