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

Question

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)

Please, don't post pictures. It would be good, if you explained the enumeration algorithm desired by words, and not just "I want this number for that row without any explanation"... — Mark Barinstein, Apr 29 '22 at 18:41
@MarkBarinstein for your help!! I am personally visual, so I thought would be great to show the picture. — SamR, Apr 29 '22 at 23:33
Please, mark the answer as accepted, if it solves the problem. — Mark Barinstein, Apr 30 '22 at 06:56

score 2 · Accepted Answer · answered Apr 29 '22 at 18:37

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

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

1 Answers1