How to include line number but show link?

Question

Let's say I have a request below:

WITH TEMP AS ( select 1 as id, 4 as value UNION SELECT 2, 53 UNION SELECT 3, 1 UNION SElECT 4, 474 UNION SELECT 5, 53 ) SELECT *, ROW_NUMBER() OVER (ORDER BY value) FROM TEMP 

This returns the following:

 3 1 1 1 4 2 2 53 3 5 53 4 4 474 5 

I would like the two lines with 53 to have the same line number (3) and the final line in order to save line number 5. I assume that this cannot be done with ROW_NUMBER. Can someone point me in the right direction to start with this?

Answer 1

Instead of using ROW_NUMBER you want to use RANK .

  SELECT *, RANK() OVER (ORDER BY value) FROM TEMP 

RANK (T-SQL) MSDN Link