Im trying to add ROW_NUMBER() in a select query on top of a calculated field and get unique rownum for unique calculated field. But Im getting sequuential rownum for all the values in calculated field.
How can I achieve it in Postgres SQL?
Thanks in advance.
select m.subscriber, count(distinct s.session_id) as num_session
,ROW_NUMBER() OVER (partition by count(distinct s.session_id) ORDER BY count(distinct s.session_id) DESC) AS rownum
from dbms.music_stream m, dbms.session s, dbms.users u
where m.session_id = s.session_id
and m.subscriber = u.user_id
and lower(u.subscription_type) = 'premium'
and lower(s.platform) = 'app'
group by m.subscriber
Im applying row_number on count(distinct s.session_id) and I expect the distinct value of count(distinct s.session_id) have same rownum but I get it in an order as below.
subscriber | num_session | rownum
------------+-------------+--------
118 | 2 | 1
73 | 2 | 2
139 | 2 | 3
59 | 2 | 4
81 | 1 | 1
103 | 1 | 2
But this is what I expect :
subscriber | num_session | rownum
------------+-------------+--------
118 | 2 | 1
73 | 2 | 1
139 | 2 | 1
59 | 2 | 1
81 | 1 | 2
103 | 1 | 2
2
Answers
You want
DENSE_RANK()
, notROW_NUMBER()
, and also do not include any partition:Note also that I replaced your old school implicit joins with modern explicit inner joins. Putting commas in the
FROM
clause is usually a bad idea.