Using Ranking Functions for Sets of Rows

Question

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.

`PARTITION BY [ID],[CODE] ORDER BY [OPEN_DATE],[CODE]` is just the same as `PARTITION BY [ID],[CODE] ORDER BY [OPEN_DATE]` as within a `[OPEN_DATE],[CODE]` partition all the Codes are the same so the secondary sort by `CODE` never adds anything — Martin Smith, Aug 08 '22 at 22:21
So you have no deterministic ordering of rows within the same `OPEN_DATE` but this seems to be required to get your desired results — Martin Smith, Aug 08 '22 at 22:23
Is it possible for, say, Code = AA, ID = 11052, and an open_date other than 2021-06-08? — Aaron Bertrand, Aug 08 '22 at 22:46

score 1 · Accepted Answer · answered Aug 08 '22 at 22:37

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.

Using Ranking Functions for Sets of Rows

1 Answers1