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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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
Code

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
Code

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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.