Increment Row_Number Only Where Distinct

Question

I have the following table, which I've made very simple because I do not know how to format it as a table on here (side note if anyone could link me to an easy tutorial on that I would be forever grateful).

id
1
1
1
2
2
2

I'd like to add another column which increments in number only on distinct IDs so the outcome should be

Id
1
1
1
2
2
2

rowNum 
1
1
1
2
2
2

Currently all I can manage to get is:

id
1
1
1
2
2
2
rowNum
1
2
3
4
5
6

I'm missing something very simple here as I'm confident I should be able to solve this issue using either row_number or rank and a window function but I cannot figure it out.

Answer 1

Use DENSE_RANK() instead of ROW_NUMBER():

SELECT
    id,
    DENSE_RANK() OVER (ORDER BY id) dr
FROM yourTable

Demo