Search code examples
sqldb2row-numberdb2-luw

ROW_NUM() OR DNSE_RANK() Which one should be used and how should I use it?

I have below table and would like to get the result in ROW_NUM Column.

I tried this but don't get what I need.

DENSE_RANK() OVER (PARTITION BY ID, DATE, SURG?, CODE  ORDER BY ID)

enter image description here

Solution 
  • SELECT 
  T.*
, DENSE_RANK () OVER (PARTITION BY ID, DATE ORDER BY "SURG?", CODE) AS ROW_NUM
FROM
(
VALUES
  (10198, 'SURG',  '06/14/2021', 58571)
, (10198, 'OTHER', '06/17/2021', 88307)  
, (10198, 'SURG',  '06/17/2021', 57425)
, (10198, 'SURG',  '06/17/2021', 57425)
, (10198, 'SURG',  '06/17/2021', 58571)
, (10198, 'SURG',  '06/17/2021', 58571)
) T (ID, "SURG?", DATE, CODE)
    ID SURG? DATE CODE ROW_NUM
    10,198 SURG 06/14/2021 58,571 1
    10,198 OTHER 06/17/2021 88,307 1
    10,198 SURG 06/17/2021 57,425 2
    10,198 SURG 06/17/2021 57,425 2
    10,198 SURG 06/17/2021 58,571 3
    10,198 SURG 06/17/2021 58,571 3