On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis <lists@xxxxxxxxxxx> 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.sabirgaliev@xxxxxx 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.sabirgaliev@xxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
