Cstdenis <lists(at)on-track(dot)ca> writes:
> I am trying to write a query that selects recent submissions (sorted by
> submission_date) but only selects the most recent one for each user_id.
> example query: /select distinct on (user_id) * from stories order by
> date_submitted desc limit 10;/
> However postgres will not allow me to filter out duplicate rows with
> distinct unless I sort on that column, which would product useless
> results for me.
Do the DISTINCT ON in a sub-query, with an ORDER BY appropriate for that
task, and then re-sort the rows the way you want them presented in the
outer query.
SELECT ... FROM
(SELECT DISTINCT ON ... ORDER BY ...) ss
ORDER BY ...;
regards, tom lane