skip to Main Content

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


  1. Chosen as BEST ANSWER
    select m.subscriber as user_id, u.first_name, u.last_name, u.gender, count(distinct s.session_id)  as num_session 
    ,DENSE_RANK() OVER (ORDER BY count(distinct s.session_id) DESC) as rank
    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, u.first_name, u.last_name, u.gender
    

  2. You want DENSE_RANK(), not ROW_NUMBER(), and also do not include any partition:

    SELECT
        m.subscriber,
        COUNT(DISTINCT s.session_id) AS num_session,
        DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT s.session_id) DESC) AS rownum
    FROM dbms.music_stream m
    INNER JOIN dbms.session s ON m.session_id = s.session_id
    INNER JOIN dbms.users u ON m.subscriber = u.user_id
    WHERE LOWER(u.subscription_type) = 'premium' AND
          LOWER(s.platform) = 'app'
    GROUP BY m.subscriber;
    

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search