Re: Distinct on a non-sort column

On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis <lists(at)on-track(dot)ca> wrote:
> 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. Group by seems to have similiar problems, plus the additional
> problem of wanting aggregate functions to be used.
>
> I even tried sorting in a subquery, but it still comes out sorted by
> user_id: select distinct on (user_id) * from stories where sid in (select
> sid from stories order by date_submitted desc limit 10);
>
>
> How can I work around this limitation to get the results sorted the way I
> want, then have the duplicates removed? I can't be the only one running into
> this limitation, there must be some workaround.
>

assuming date_submitted are unique for user_id:
select * from stories s, (select user_id, max(date_submitted) d from
stories group by user_id) ss where s.user_id = ss.user_id and
s.date_submitted = ss.date_submitted;

--
с уважением,
Таир Сабыргалиев
ТОО "BEE Software"
Республика Казахстан, 010000
г.Астана, ул.Сарайшык 34, ВП-27
Тел.: +7 (7172) 56-89-31
Сот.: +7 (702) 2173359
e-mail: tair(dot)sabirgaliev(at)bee(dot)kz
Tair Sabirgaliev
"BEE Software" Ltd.
Republic of Kazakhstan, 010000
Astana, Sarayshyk str. 34, sect. 27
Tel.: +7 (7172) 56-89-31
Mob.: +7 (702) 2173359
e-mail: tair(dot)sabirgaliev(at)bee(dot)kz