Ordering Students According to Exam Scores Grouped by Their Classes Using DENSE_RANK() T-SQL Functions
Today, I was requested to solve a problem in a t-sql query which was assumed to point some software according to their usage capacities such as number of records, used disk space, left disk size, etc. on different servers.
And each criteria has diffent weight when compared to other criterias.
After each software instance installed on a server is calculated to sum up the resultant overall score I was asked to get the best ones in each server.
I was able to solve the problem by using one of the new T-SQL window functions in
SQL Server 2005, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
ROW_NUMBER() is a powerful function and if it is used with PARTITION BY it can solve numbering items within groups divided by column values for example.
Here is a sample using multiple DENSE_RANK() t-sql window functions within the same sql select query.
CREATE TABLE Points
(
ClassName varchar(20),
StudentName varchar(20),
Exam1 int,
Exam2 int,
Exam3 int
)
GO
insert into points select 'Class A', 'Matt', 10, 90, 190
insert into points select 'Class B', 'John', 2, 50, 700
insert into points select 'Class C', 'Bobby', 6, 30, 300
insert into points select 'Class A', 'Drew', 3, 22, 250
insert into points select 'Class B', 'Erwin', 1, 15, 580
insert into points select 'Class C', 'Sue', 6, 35, 490
GO
SELECT
ClassName,
StudentName,
Exam1,
Exam1_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam1 DESC) * (1),
Exam2,
Exam2_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam2 DESC) * (2),
Exam3,
Exam3_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam3 DESC) * (3)
FROM Points
The above script will return the students and their exam results and the sort order of each exams result compared among other results in his/her class. Each exam has different weight (1, 2, 3 in this example). And the student with least exam score gets 1 * (weight of the exam), and the later student gets 2 * (weight of the exam) points.
If we want to get the best students in each class, then we can use the below query.
SELECT
ClassName,
StudentName,
Total_Points
FROM
(
SELECT
ClassName,
StudentName,
Total_Points = Exam1_Point + Exam2_Point + Exam3_Point,
SortOrder = DENSE_RANK() OVER (
PARTITION BY ClassName
ORDER BY (Exam1_Point + Exam2_Point + Exam3_Point) DESC
)
FROM (
SELECT
ClassName,
StudentName,
Exam1_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam1 ASC) * 1,
Exam2_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam2 ASC) * 2,
Exam3_Point =
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Exam3 ASC) * 3
FROM Points
) RawPoints
) Results
WHERE SortOrder = 1
The reason why I preferred DENSE_RANK() to ROW_NUMBER() is there are some cases where some of the grouped students might have the same scores. In this case if ROW_NUMBER() is used then one student will be on top of the others although they have the same scores. DENSE_RANK() assigns the same order number to the group of students whose scores are identical.