SQL Ranking Functions Explained [Practical Examples]

Overview of SQL Ranking Functions

SQL Ranking functions are window functions used to assign the ranking value for each record in the partition of a result set, the ranking value is depended upon the rank function that is used, SQL Ranking functions are nondeterministic

In the SQL Ranking functions, we use the OVER() clause which define a set of rows in the result set. We can also use SQL PARTITION BY clause to define a subset of data in a partition. You can also use Order by clause to sort the results in a descending or ascending order.

 

Different SQL Ranking Functions

There are four ranking window functions supported in SQL Server

1. RANK()
2. NTILE()
3. DENSE_RANK()
4. ROW_NUMBER()

 

1. SQL RANK() Function

SQL RANK() function is used to assign the rank of each row within the partition of result set, The rank of a row is one plus the number from the rank number of row before in question

The same rank is assigned to the rows in a partition which have the same values. The rank of the first row is 1. The ranks may not be consecutive in the RANK() function as it adds the number of repeated rows to the repeated rank to calculate the rank of the next row

 

SQL RANK() Function Syntax

RANK() OVER (
   [PARTITION BY column_name, ]
   ORDER BY column_name (ASC | DESC) );	

 

Here,

• OVER : Over is a keywords used to specified the column name on which ranking will applied
• PARTITION BY column_name : The PARTITION BY clause divides the result record set into partitions to which the function is applied
• ORDER BY column_name : The ORDER BY clause specifies the sort order of records in each partition to which the RANK() is applied

 

SQL RANK() Function Examples

Consider student result management system with three tables’ student, result and subject to perform practical examples on SQL RANK Functions

Student Table

Result Table

Subject Table

 

Example-1: SQL RANK() Function with ORDER BY

Write SQL query to rank student records based on student enrollment number

SELECT student_id,studentname,enrollmentno,RANK () OVER (ORDER BY enrollmentno) as 'Rank by Enrollment'
FROM tblstudent

• In the above query, SQL  RANK() function is used to assign the row number to resulting record set from student table in the order of enrollment number
• The output of above query contains one extra column with title ‘Rank by Enrollment’ with rank number of each row in increment order starts from 1

OUTPUT:

 

Example-2: SQL RANK() Function with  PARTITION BY

Write SQL query to display student result data in the descending order of percentage by assigning rank value by partitioning records based on examname

SELECT result_id, tblresult.student_id, examname, examdate, studentname,RANK()  OVER( PARTITION BY  examname ORDER BY pecentage desc ) as 'RANK of Student'
FROM tblresult,tblstudent where tblresult.student_id=tblstudent.student_id

• In the above query, SQL RANK() Function with partition by clause is applied to each row in each partition and reinitialized when crossing the partition’s boundary of each examname
• The output of above query contains rank value for each exam name starts from 1 , in the order of percentage

OUTPUT:

 

Example-3: SQL RANK() OVER with  PARTITION BY & Where condition

Write SQL query  to assign rank number to each semester student if the student status is pass

SELECT result_id, tblresult.student_id, examname, examdate, studentname,RANK()  OVER( PARTITION BY  examname ORDER BY pecentage desc ) as 'RANK of Student'
FROM tblresult,tblstudent where tblresult.student_id=tblstudent.student_id and tblresult.status='pass';

• In the above query, SQL RANK() function is applied with PARTITION BY clause to assign rank number to each partition of examname starts from 1
• Records will be display in descending order of percentage so higher percentage student will get top ranks value
• SQL Where clause is applied with RANK function to conditionally retrieve records

OUTPUT:

 

2. SQL NTILE() Function

SQL NTILE() function is used to divide the resulting record set of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one.

For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

 

SQL NTILE() Function Syntax

NTILE(no_of_buckets) OVER (
    [PARTITION BY column_name, ... ]
     ORDER BY column_name [ASC | DESC], ...
)

Here,

• no_of_buckets : no_of_buckets is a positive integer number or an expression specify no of buckets in which records will be distributed
• PARTITION BY column_name : The PARITITION BY clause divides the result set returned from the FROM clause into partitions to which the NTILE() function is applied.
• ORDER BY column_name : The ORDER BY clause specifies the sort order of records in each partition to which the NTILE() is applied

 

Example-4: SQL NTILE() Function with ORDER BY

Write SQL query to divide student result data into three groups  of semester and assign rank to each student based on percentage

SELECT  NTILE(3) OVER( order by examname ) as 'Groups',tblresult.student_id,studentname,examname 
from tblresult,tblstudent where tblstudent.student_id=tblresult.student_id

• In the above query, SQL NTILE(3) function will divide resulting record set of student result into three equal groups  and assign group number starts from one
• Order by examname will order the resulting record in the order of semester

OUTPUT:

 

Example-5: SQL NTILE() Function with PARTITION BY

Write SQL query to assign rank number to each semester students in the order of their percentage

SELECT  NTILE(3) OVER( partition by examname order by pecentage ) as 'Rank',tblresult.student_id,studentname,examname 
from tblresult,tblstudent where tblstudent.student_id=tblresult.student_id

• In the above query, SQL NTILE() function with partition by clause to make partition of resulting record set based on the semesters
• Order by clause will sort the resulting records based on percentage
• SQL NTILE() function assign the rank to each semester student result record in the increment order starts from one

OUTPUT:

 

3. SQL DENSE_RANK() Function

SQL DENSE_RANK() function assign the rank value to each record within a result set partition, with no gaps in the ranking values. The rank of a specific record is one plus the number of distinct rank values that come before that specific row,

SQL DENSE_RANK () function assign rank value based on the order by clause column, if records has same value in order by column than SQL DENSE_RANK () function will assign same value

If two or more records have the same rank value in the same partition, each of those rows will receive the same rank

 

SQL DENSE_RANK() Function Syntax

DENSE_RANK ( ) OVER (
    [PARTITION BY column_name, ... ]
    ORDER BY column_name [ASC | DESC], ...
)

Here,

• PARTITION BY column_name : The PARITITION BY clause divides the result set returned from the FROM clause into partitions to which the DENSE_RANK() function is applied.
• ORDER BY column_name : The ORDER BY clause specifies the sort order of records in each partition to which the DENSE_RANK() is applied

 

Example-6: SQL DENSE_RANK() Function with ORDER BY

Write SQL query to assign rank to the student records based on their subject in the same semester

SELECT  DENSE_RANK() OVER(order by examname) as 'Rank',tblresult.student_id,examname as 'Semester',tblresult.subjectid,subjectname,pecentage as 'Percentage',grade  
from tblresult,tblsubject where tblresult.subjectid=tblsubject.subjectid

• In the above query, SQL DENSE_RANK() function is applied to assign the rank value to each student semester and subject wise records in the order of examname
• The output of above query contains rank column having rank number starts from one in increment order for each subject marks of each student

OUTPUT:

 

Example-7: SQL DENSE_RANK() Function with PARTITION BY

Write SQL query to assign rank to the student records of each semester based on descending order of percentage

SELECT  DENSE_RANK() OVER(partition by examname order by pecentage) as 'Rank',tblresult.student_id,examname as 'Semester',tblresult.subjectid,subjectname,pecentage as 'Percentage',grade  
from tblresult,tblsubject where tblresult.subjectid=tblsubject.subjectid

• In the above query, SQL DENSE_RANK() function is used to assign the rank to each semester student based on the percentage
• The output of above query contains rank column having rank number starts from one in increment order for each semester based on the percentage

OUTPUT:

 

4. SQL ROW_NUMBER () FUNCTION

SQL ROW_NUMBER() function will assign the count row numbers the output of a result set, function assign the sequential number of a records within a partition of a result set, starting at 1 for the first row in each partition

SQL ROW_NUMBER () Values of the partitioned column are unique. SQL Rank () function assign Values of the ORDER BY columns are unique also the combinations of values of the partition column and ORDER BY columns are unique

 

SQL ROW_NUMBER() Syntax

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

 

Here,

• PARTITION BY column_name: Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.  If PARTITION BY is not specified, the function treats all rows of the query result set as a single group. For more information, see OVER Clause (Transact-SQL).
• ORDER BY column_name : The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required

 

Example-8: SQL ROW_NUMBER() Function with ORDER BY

Write SQL query to assign the count row number to the student list in the order of city name

SELECT  student_id, ROW_NUMBER() OVER(order by city) as 'RECORD NUMBER',studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city
FROM  tblstudent

In the above query, SQL ROW_NUMBER() function used to assign the record number value to each record order of city name

OUTPUT:

 

Example-9: SQL ROW_NUMBER() Function with PARTITION BY

Write SQL query to assign the record counting number of student city wise in the order of enrollment number

SELECT row_number() OVER (partition BY city ORDER BY enrollmentno) as 'Citywise student', studentname, city, enrollmentno
FROM tblstudent

• In the above query, SQL RANK_NUMBER() function is used to assign the record count number of student based on city name
• Partition by clause is used to make partition of student records city wise and order by clause is used to display resulting records in the ascending order of enrollment number

OUTPUT:

 

Summary

In this article of SQL Rank functions , we have explained Overview of SQL Ranking functions , four different type of SQL Ranking functions - RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() with syntax and practical examples of SQL RANK() function with Order by and partition by clause , SQL NTILE() function with order and partition by clause , SQL DENSE_RANK() function with order by and partition by clause and SQL Row_number() function with order by and partition by clause

 

References

SQL FUNCTIONS

 

Read More

SQL Ranking Functions

 

Related Keywords: sql rank, rank in sql, rank sql, rank function in sql, ranking functions sql, sql rank over, sql rank function