Applying grouped ranking using ROW_NUMBER

Question

I m Looking for ways to assign the row numbers as below for the table

Roll No    Name    Score 
 1           ABC    10
 1           ABC    10
 1           DEF     8
 2           ASC     9
 2           YHN     4
 3           IOP     5
 3           YHN     4

I m looking for a way to assign the roll no as Rownumber()

Roll No    Name    Score    Row_Number
 1           ABC    10        1
 1           ABC    10        2
 1           DEF     8        3
 2           ASC     9        1
 2           YHN     4        2
 3           IOP     5        1 
 3           YHN     4        2

I m trying to work around with Row_number() , it is isnt working . ANy inputs on this world be great :)

Thanks !!!!

Answer 1

SELECT [Roll No], Name, Score, [ROW_NUMBER] = 
  ROW_NUMBER() OVER (PARTITION BY [Roll No] ORDER BY Score DESC)
FROM dbo.table
ORDER BY [Roll No], [ROW_NUMBER];

If you later decide that you want to handle ties in a different way, play with using RANK() or DENSE_RANK() in place of ROW_NUMBER()...