John Hass John Hass - 29 days ago 14
SQL Question

How to assign unique ID for group of duplicates?

I am not asking how to delete duplicates.

I want to assign unique ID for group of duplicates:

A A
A A
A B
B B
B B

A A 1
A A 1
A B 2
B B 3
B B 3

Answer

You are looking for dense_rank():

select t.*,
       dense_rank() over (order by col1, col2) as newcol
from t;