[SOLVED] Using Ranking Functions for Sets of Rows

Using Ranking Functions for Sets of Rows

I have the following syntax which created the below table:

SELECT [OPEN_DATE]
      ,[CODE]
      ,[ID]
      ,ROW_NUMBER() OVER (PARTITION BY [ID],[CODE] ORDER BY [OPEN_DATE],[CODE]) AS [RowOrder]
FROM [Table]


OPEN_DATE   CODE    ID  RowOrder
6/8/2021    AA  11052   1
6/8/2021    AA  11052   2
6/8/2021    AA  11052   3
6/8/2021    AB  11052   1
6/8/2021    AB  11052   2
6/8/2021    AB  11052   3
7/15/2021   AC  89321   1
7/15/2021   AC  89321   2
7/15/2021   AC  89321   3
9/24/2022   AD  89321   1
9/24/2022   AD  89321   2
9/24/2022   AD  89321   3

I need to add column to number these in sets/groups like so:

OPEN_DATE   CODE    ID  RowOrder    SetOrder
6/8/2021    AA  11052   1           1
6/8/2021    AA  11052   2           1
6/8/2021    AA  11052   3           1
6/8/2021    AB  11052   1           2
6/8/2021    AB  11052   2           2
6/8/2021    AB  11052   3           2
7/15/2021   AC  89321   1           1
7/15/2021   AC  89321   2           1
7/15/2021   AC  89321   3           1
9/24/2022   AD  89321   1           2
9/24/2022   AD  89321   2           2
9/24/2022   AD  89321   3           2

Notice SetOrder increases by 1 when Code changes but ID stays the same, and then it resets to 1 when ID changes.

I tried using ROW_NUMBER again as well as DENSE_RANK and RANK, but none of them seem to work. Is there another function I haven't thought of?

Thank you.

Solution

You can use DENSE_RANK for this

SELECT OPEN_DATE
      ,CODE
      ,ID
      ,ROW_NUMBER() OVER (PARTITION BY ID, CODE ORDER BY OPEN_DATE) AS RowOrder
      ,DENSE_RANK() OVER (PARTITION BY ID ORDER BY CODE) AS SetOrder
FROM [Table]

db<>fiddle

Note that CODE in the ORDER BY of RowOrder makes no sense, as it's already in the PARTITION BY.