Applying a group rating using ROW_NUMBER
I m Looking for ways to assign row numbers as shown below for a 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 am looking for a way to assign 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 with Row_number (), it doesn't work. ANY INPUTS in this world will be great :)
Thank!!!!
+3
source to share
1 answer
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 communications differently, play with RANK()
or DENSE_RANK()
instead of ROW_NUMBER()
...
+3
source to share