How to enumerate rows by division?-CodePudding

I have the following table

id num sub_id
1   3    1
1   5    2
1   1    1
1   4    2
2   1    5
2   2    5

I want to get this result

id num sub_id  number 
1   3    1       1 
1   5    2       2
1   1    1       1 
1   4    2       2
2   1    5       1
2   2    5       1

I tried to do this row_number() over (partition by id order by num,sub_id DESC) but th result is obviosly differs

CodePudding user response：

I don't understand your business because you don't explain your logic and information about that, but maybe this query helps you?

Result and info: dbfiddle

with recursive
    cte_r as (
        select id,
               num,
               sub_id,
               row_number() over () as rn
        from test),
    cte as (
        select id,
               num,
               sub_id,
               rn,
               rn as grp
        from cte_r
        where rn = 1
        union all
        select cr.id,
               cr.num,
               cr.sub_id,
               cr.rn,
               case
                   when cr.id != c.id then 1
                   when cr.id = c.id and cr.sub_id = c.sub_id then c.grp
                   when cr.id = c.id and cr.sub_id > c.sub_id then c.grp   1
                   when cr.id = c.id and cr.sub_id < c.sub_id then 1
                   end
        from cte c,
             cte_r cr
        where c.rn = cr.rn - 1)
select id,
       num,
       sub_id,
       grp
from cte
order by id

CodePudding user response：

It looks like you actually want to ignore the num column and then use DENSE_RANK on sub_id:

SELECT *, dense_rank() AS number OVER (PARTITION BY id ORDER BY sub_id) FROM …;