CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
user2302158
user2302158

Reputation: 463

How to get same rownumber() for same values

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

Upvotes: 4

Views: 7160

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272396

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.

Upvotes: 12

Related Questions