Distinct on a non-sort column
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.
On Sun, Nov 6, 2011 at 12:39 AM, Cstdenis <lists@on-track.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.sabirgaliev@bee.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.sabirgaliev@bee.kz
Cstdenis <lists@on-track.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
On 11/05/11 11:39 AM, Cstdenis wrote:
example query: /select distinct on (user_id) * from stories order by
date_submitted desc limit 10;/
select user_id,max(date_submitted) from stories group by date_submitted;
?
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 11/5/2011 12:49 PM, Tom Lane wrote:
Cstdenis<lists@on-track.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
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;
I think it has the problem of which of the stories by that user is
selected is random rather than the most recent being guaranteed (because
the distinct is done before the sort). Or am I misunderstanding this?
The suggestions by others of using max(date_submitted) may be a good
workaround for this, but I also need to do the same thing sorted by a
calculated score value which I do not think will be sufficiently unique
for Tair's suggestion.
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
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).