Transact SQL: Using partition by with row_number()
I stumbled across something interesting today when looking at the documentation for Transact SQL's row_number() function. In the past, when using row_number(), I had limited myself to simple scenarios only using the over clause with "order by". Here is an example where I take a simple resultset containing 7 grades and rank them in descending order:
-
SELECT row_number() over(ORDER BY grades.grade DESC) AS grade_rank, grades.grade
-
FROM
-
(
-
SELECT 99 AS grade
-
union
-
SELECT 87
-
union
-
SELECT 71
-
union
-
SELECT 83
-
union
-
SELECT 78
-
union
-
SELECT 63
-
union
-
SELECT 92
-
) grades
In the documentation found here I found mention of using not only "order by", but also something I was unfamiliar with called "partition by", within the over clause. Upon reading further, I learned that this can be of value if you wanted to apply some sort of rank or sequence to subsets within a resultset rather than the entire resultset. A relevant example I came up with was taking a resultset that contains grades for multiple students in multiple classes and ranking the students' grade in descending order within each class. Here is the query:
-
SELECT classid, studentid, row_number() over(partition BY classid ORDER BY classid ASC, grade DESC) AS class_rank, grade
-
FROM
-
(
-
SELECT 1 AS classid, 1 AS studentid, 99 AS grade
-
union
-
SELECT 1 AS classid, 2 AS studentid, 87 AS grade
-
union
-
SELECT 1 AS classid, 3 AS studentid, 71 AS grade
-
union
-
SELECT 2 AS classid, 4 AS studentid, 83 AS grade
-
union
-
SELECT 2 AS classid, 5 AS studentid, 78 AS grade
-
union
-
SELECT 2 AS classid, 6 AS studentid, 63 AS grade
-
union
-
SELECT 2 AS classid, 7 AS studentid, 92 AS grade
-
) student_grades
Running this query will show you 3 students ranked in order of grade descending for class 1 and 4 students ranked in order of grade descending for class 2. Pretty sweet, huh?