SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems