How to get same rownumber() for same values

Question

I need to get the same row number if the values gets repeated in column week and desc. For the following table:

week	desc
1	ff
1	ss
1	ss
2	ff
2	ss
4	ff
4	ff
4	ss
4	ss

The expected result is:

week	desc	rownum
1	ff	1
1	ss	2
1	ss	2
2	ff	1
2	ss	2
4	ff	1
4	ff	1
4	ss	2
4	ss	2

Answer 1

You want DENSE_RANK instead of ROW_NUMBER:

SELECT Week
     , [Desc]
     , DENSE_RANK() OVER (PARTITION BY Week ORDER BY [Desc]) AS [Rank #]
FROM t

DENSE_RANK and RANK assign same value to rows with tie in the order by columns. DENSE_RANK in addition assigns "dense" rank numbers instead of "gapped" numbers.