Table of Contents
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
- RANK()
- NTILE()
- DENSE_RANK()
- 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
student_id | studentname | admissionno | admissiondate | enrollmentno | date_of_birth | city | class_id | |
---|---|---|---|---|---|---|---|---|
101 | reema | 10001 | 02-02-2000 | e15200002 | 02-02-1990 | reema@gmail.com | surat | 2 |
102 | kriya | 10002 | 04-05-2001 | e16200003 | 04-08-1991 | kriya@gmail.com | surat | 1 |
103 | meena | 10003 | 06-05-1999 | e15200004 | 02-09-1989 | meena@gmail.com | vadodara | 3 |
104 | carlin | 2001 | 04-01-1998 | e14200001 | 04-04-1989 | carli@gmail.com | vapi | 1 |
105 | dhiren | 2002 | 02-02-1997 | e13400002 | 02-02-1987 | dhiru@gmail.com | vapi | 2 |
106 | hiren | 2003 | 01-01-1997 | e13400001 | 03-03-1887 | hiren@gmail.com | surat | 2 |
107 | mahir | 10004 | 06-09-2000 | e15200003 | 07-09-1990 | mahi@gmail.com | vapi | 3 |
108 | nishi | 2004 | 02-04-2001 | e16200001 | 03-02-1991 | nishi@gmail.com | vadodara | 1 |
Result Table
result_id | student_id | examname | examdate | subject | obtainmark | totalmarks | percentage | grade | status |
---|---|---|---|---|---|---|---|---|---|
3001 | 101 | sem1 | 07-08-2001 | 1 | 80 | 100 | 80 | A+ | pass |
3002 | 101 | sem1 | 08-08-2001 | 2 | 76 | 100 | 76 | A+ | pass |
3003 | 102 | sem3 | 05-05-2000 | 3 | 67 | 100 | 67 | A | pass |
3004 | 102 | sem3 | 06-05-2000 | 4 | 89 | 100 | 89 | A+ | pass |
3005 | 102 | sem3 | 07-05-2000 | 5 | 90 | 100 | 90 | A+ | pass |
3006 | 103 | sem5 | 08-09-1998 | 6 | 55 | 100 | 55 | B | pass |
3007 | 103 | sem5 | 09-09-1998 | 7 | 30 | 100 | 30 | D | fail |
3008 | 103 | sem5 | 10-09-1998 | 8 | 34 | 100 | 34 | D | fail |
Subject Table
subjectid | facultyname | subjectname | subjectcode |
---|---|---|---|
1 | krishna | c | 1003 |
2 | rahul | cpp | 1004 |
3 | radha | asp | 1005 |
4 | meera | sql | 1006 |
5 | yasoda | cloud | 1007 |
6 | nadan | cg | 1008 |
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
Read More
Related Keywords: sql rank, rank in sql, rank sql, rank function in sql, ranking functions sql, sql rank over, sql rank function