###
What
is Ranking Function?

###

In
SQL Server Ranking functions are used to give a rank to each record among all
records or in a group.

Let’s
understand what does this means. I will take a simple real world example to
make you understand this. Let’s go to in your school days. Our class teacher
maintains a register in which all student names are written. Each student is
given a roll number based on their names for example if there are 50 students
then each student will get one roll number between 1 to 50, this is
nothing but teacher has given a serial number to each student. When your result
comes out, based on student marks now student will be given rank, which will be
different from roll number (serial number) i.e roll number 10 came first, roll
number 21 cane second and so on. This is nothing but teacher has given a rank
based on the student marks. In some case suppose two students get the same
marks, then same rank will be given to both the students.

All
Ranking functions are non deterministic function, for more information
see Deterministic and Non Deterministic functions in SQLServer.

**What
are the different Ranking Functions in SQL Server?**

In
SQL Server there are four types of Ranking Functions

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

Let’s
understand each ranking function one by one.

Before
we start, let's populate some data into our **TEACHMESQLSERVER** Database.
If you don’t have database created then use the below query to create.

CREATE DATABASE TEACHMESQLSERVER

Let’s
create one table name as EMP in our database and populate data. To create and
populate data you can run below set of SQL statements.

USE TEACHMESQLSERVER
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='EMP')
DROP TABLE EMP
GO
CREATE TABLE EMP(EMPNAME VARCHAR(MAX),DEPARTMENT VARCHAR(MAX), SALARY MONEY)
GO
INSERT INTO EMP VALUES ('GHANESH','IT',18000)
INSERT INTO EMP VALUES ('PRASAD','HR',2000)
INSERT INTO EMP VALUES ('GAUTAM','SALES',5000)
INSERT INTO EMP VALUES ('KUMAR','HR',85000)
INSERT INTO EMP VALUES ('SUMIT','IT',18000)
INSERT INTO EMP VALUES ('PRIYANKA','HR',25000)
INSERT INTO EMP VALUES ('KAPIL','SALES',5000)
INSERT INTO EMP VALUES ('GOLDY','HR',12000)
INSERT INTO EMP VALUES ('SACHIN','IT',21500)
INSERT INTO EMP VALUES ('OJAL','SALES',19500)
INSERT INTO EMP VALUES ('ISHU','HR',28000)
INSERT INTO EMP VALUES ('GAURAV','IT',15500)
INSERT INTO EMP VALUES ('SAURABH','SALES',20500)
INSERT INTO EMP VALUES ('MADHU','IT',18000)
INSERT INTO EMP VALUES('ATUL','SALES',35000)

GO
SELECT * FROM EMP

**What are the different Ranking Functions in SQL Server?**

**TEACHMESQLSERVER**Database. If you don’t have database created then use the below query to create.

As you can see from the above query result set, data has been successfully populated.

I will be using this data set in coming examples to explain all ranking functions.

I will be using this data set in coming examples to explain all ranking functions.

**RANK**

Rank function returns the rank of each record among all records or in a partition.

If two or more rows tie for a rank, each tied rows receives the same rank but RANK

function does not always return consecutive integers. Don’t worry if second point is not

clear to you I will explain this with example.

If two or more rows tie for a rank, each tied rows receives the same rank but RANK

function does not always return consecutive integers. Don’t worry if second point is not

clear to you I will explain this with example.

Syntax:

**RANK () OVER (PARTITION BY [column_list] ORDER BY [column_list])****PARTITION BY [Column_list]**– This is optional if you want to divide the result set into

partitions to which the function is applied then must give the column name,

if you don’t use this clause then the function treats all records of the query result set as

a single group.

ORDER BY [Column_list] – This is not optional it is must clause; this determines the order of

the data before the function is applied.

the data before the function is applied.

Let’s understand the RANK function using example.

__Problem 1:__
You want to get each employee rank among all other employees based on their highest

salary.

salary.

__Solution:__
You can use RANK function without PARTITION By Clause and ORDER BY Clause on SALARY

in Descending order.

in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , RANK() OVER(ORDER BY SALARY

DESC) AS OVER_ALL_RANK_BY_SALARY FROM EMP

DESC) AS OVER_ALL_RANK_BY_SALARY FROM EMP

__Explanation:__
As
you can see from the result set each employees has given a rank based on their
salary.

Employee KUMAR has the highest Salary rank is 1 and PRASAD has the lowest salary rank is

15. If you notice Employee MADHU, SUMIT and GHANESH have given rank 8 because all

three has the same salary. Employee GAURAV has given 11 because 8 rank can be given

to a least one person but two more employees shared the same rank so next two ranks 9

and 10 will not be given to any employee. This is the problem with the RANK Function it

does not always return consecutive integers.

Employee KUMAR has the highest Salary rank is 1 and PRASAD has the lowest salary rank is

15. If you notice Employee MADHU, SUMIT and GHANESH have given rank 8 because all

three has the same salary. Employee GAURAV has given 11 because 8 rank can be given

to a least one person but two more employees shared the same rank so next two ranks 9

and 10 will not be given to any employee. This is the problem with the RANK Function it

does not always return consecutive integers.

__Problem 2:__
You
want to get each employee rank in their department based on their highest
salary.

__Solution:__
You
can use RANK function with PARTITION BY Clause on
DEPARTMENT

and
ORDER BY Clause on SALARY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , RANK() OVER(PARTITION BY DEPARTMENT

ORDER BY SALARY DESC) ASRANK_IN_DEP_BY_SALARY FROM EMP

ORDER BY SALARY DESC) ASRANK_IN_DEP_BY_SALARY FROM EMP

__Explanation:__
As
you can see from the result set each employees has given a rank based on their
salary

in their department. In HR Department every employee has distinct salary so each has given

different rank but in IT Department three employees has same salary so they has given rank

2 and next employee gets rank 5.This is the problem with the RANK Function it does not

always return consecutive integers.

in their department. In HR Department every employee has distinct salary so each has given

different rank but in IT Department three employees has same salary so they has given rank

2 and next employee gets rank 5.This is the problem with the RANK Function it does not

always return consecutive integers.

**DENSE_RANK**

RANK
function does not always return consecutive integers to overcome this problem
we

have another Ranking function in SQL Server which is known as DENSE_RANK. DENSE_RANK

function Returns the rank of rows within the partition of a result set or within all records,

without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks

that come from the previous row.

have another Ranking function in SQL Server which is known as DENSE_RANK. DENSE_RANK

function Returns the rank of rows within the partition of a result set or within all records,

without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks

that come from the previous row.

Syntax:

**DENSE_RANK () OVER (PARTITION BY [column_list] ORDER BY [column_list])**
PARTITION
BY [Column_list] – This is optional if you want to divide the result set into

partitions to which the function is applied then you must give the column name, if you

don’t use this clause then the function treats all records of the query result set as a single

group.

partitions to which the function is applied then you must give the column name, if you

don’t use this clause then the function treats all records of the query result set as a single

group.

ORDER
BY [Column_list] – This is not optional it is a must clause; this determines
the order

of the data before the function is applied.

of the data before the function is applied.

Let’s
understand the DENSE_RANK function using the old examples.

__Problem 1:__
You
want to get each employee rank without gap among all other employees based on
their

highest salary.

highest salary.

__Solution:__
You
can use DENSE_RANK function without PARTITION By Clause and ORDER BY Clause on

SALAY in Descending order.

SALAY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , DENSE_RANK() OVER(ORDER BY SALARY DESC)

AS OVER_ALL_RANK_BY_SALARY FROMEMP

AS OVER_ALL_RANK_BY_SALARY FROMEMP

Explanation:Explanation:

As
you can see from the result set each employees has given a rank based on their
salary.

Whoever is having same salary has been given same rank i.e 8 and 11 but there is no gap in

the ranking like RANK function.

Whoever is having same salary has been given same rank i.e 8 and 11 but there is no gap in

the ranking like RANK function.

__Problem 2:__
You
want to get each employee rank without any gap in their department based on
their

highest salary.

highest salary.

__Solution:__
You
can use DENSE_RANK function with PARTITION BY Clause on
DEPARTMENT and

ORDER BY Clause on SALAY in Descending order.

ORDER BY Clause on SALAY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , DENSE_RANK() OVER(PARTITION BY

DEPARTMENT ORDER BY SALARY DESC) ASRANK_IN_DEP_BY_SALARY FROM EMP

DEPARTMENT ORDER BY SALARY DESC) ASRANK_IN_DEP_BY_SALARY FROM EMP

__Explanation:__
As you can see from the result set each employees has given a rank based on their salary

in their department. In HR Department every employee has distinct salary so each has

given different rank but in IT Department three employees has same salary so they has

given rank 2 and next Employee gets rank as 3.

in their department. In HR Department every employee has distinct salary so each has

given different rank but in IT Department three employees has same salary so they has

given rank 2 and next Employee gets rank as 3.

**ROW_NUMBER**

ROW_NUMBER function Returns the serial number of each record within a partition of a

result set or within all records. It starts serial number as 1 for the first record and for next

records it adds one. We get unique serial number for each record if using ROW_NUMBER

function. If we are using ROW_NUMBER function within a partition of a result set then it

starts with 1 in each partition.

result set or within all records. It starts serial number as 1 for the first record and for next

records it adds one. We get unique serial number for each record if using ROW_NUMBER

function. If we are using ROW_NUMBER function within a partition of a result set then it

starts with 1 in each partition.

Syntax:

**ROW_NUMBER () OVER (PARTITION BY [column_list] ORDER BY [column_list])**
PARTITION BY [Column_list] – This is optional if you want to divide the result set into

partitions to which the function is applied then you must give the column name,

if you don’t use this clause then the function treats all records of the query result set

as a single group.

partitions to which the function is applied then you must give the column name,

if you don’t use this clause then the function treats all records of the query result set

as a single group.

ORDER BY [Column_list] – This is not optional it is a must clause; this determines the

order of the data before the function is applied.

order of the data before the function is applied.

Let’s understand the ROW_NUMBER function with simple examples.

__Problem 1:__
You want to get serial number for each employee among all employees based on their

highest salary.

highest salary.

__Solution:__
You can use

SALAY in Descending order.

**ROW_NUMBER**function without PARTITION By Clause and ORDER BY Clause onSALAY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY

DESC) AS ROW_NUMBER_BY_SALARY FROM EMP

DESC) AS ROW_NUMBER_BY_SALARY FROM EMP

__Explanation:__
As you can see from the result set each employees has given a serial number based on their

salary among all employees. Serial number starts with 1 and goes till 15 (because we have

total 15 records in EMP table).Whoever is having same salary has not given same serial

number like DENSE_RANK function.

salary among all employees. Serial number starts with 1 and goes till 15 (because we have

total 15 records in EMP table).Whoever is having same salary has not given same serial

number like DENSE_RANK function.

__Problem 2:__
You want to get serial number for each employee in their department based on their

highest salary.

highest salary.

__Solution:__
You can use

ORDER BY Clause on SALARY in Descending order.

**ROW_NUMBER**function with PARTITION BY Clause on DEPARTMENT andORDER BY Clause on SALARY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY, ROW_NUMBER() OVER(PARTITION BY

DEPARTMENT ORDER BY SALARY DESC) AS ROW_NUMBER_IN_DEP_BY_SALARY FROM EMP

DEPARTMENT ORDER BY SALARY DESC) AS ROW_NUMBER_IN_DEP_BY_SALARY FROM EMP

__Explanation:__
As you can see from the result set each employee has given a serial number based on their

salary in their department and it’s unique, it starts with 1 and goes till 5 because each

department has max 5 employees.

Integer_Expression – It is a positive integer constant expression that specifies the number of groups

into which each partition must be divided

salary in their department and it’s unique, it starts with 1 and goes till 5 because each

department has max 5 employees.

**NTILE**

NTILE function distributes all your records in an ordered groups with a specified group

number. The groups are numbered, starting at 1 for the first group and adds 1 for the next

group. For each record, NTILE returns the number of the group to which the record belongs.

number. The groups are numbered, starting at 1 for the first group and adds 1 for the next

group. For each record, NTILE returns the number of the group to which the record belongs.

Syntax:

**NTILE (Interger_Expression) OVER (PARTITION BY [column_list] ORDER BY [column_list])**Integer_Expression – It is a positive integer constant expression that specifies the number of groups

into which each partition must be divided

PARTITION BY [Column_list] – This is optional if you want to divide the result set into partitions to

which the function is applied then you must give the column name, if you don’t use this clause then

the function treats all records of the query result set as a single group.

which the function is applied then you must give the column name, if you don’t use this clause then

the function treats all records of the query result set as a single group.

ORDER BY [Column_list] – This is not optional it is a must clause; this determines the order of the

data before the function is applied.

data before the function is applied.

Let’s understand the NTILE function with simple examples.

__Problem 1:__
You want to divide all employees into 3 groups based on their highest salary.

__Solution:__
You can use

and ORDER BY Clause on SALAY in Descending order.

**NTILE**function without PARTITION By Clause and with Inter_expression equals to 3and ORDER BY Clause on SALAY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , NTILE(3) OVER(ORDER BY SALARY

DESC) AS GROUP_NUMBER_BY_SALARY FROM EMP

DESC) AS GROUP_NUMBER_BY_SALARY FROM EMP

__Explanation:__
As you can see from the result set there are total 15 records in EMP table. All records have been

divided into three groups. Each group has it group number with 5 records.

divided into three groups. Each group has it group number with 5 records.

__Problem 2:__
You want divide each employee in their department based on their highest salary into 2 groups.

__Solution:__
You can use

DEPARTMENT and ORDER BY Clause on SALAY in Descending order.

**NTILE**function with INTEGER EXPRESSION value equal to 2, PARTITION BY Clause onDEPARTMENT and ORDER BY Clause on SALAY in Descending order.

__SQL Code:__
SELECT EMPNAME, DEPARTMENT, SALARY , NTILE(3) OVER(PARTITION BY

DEPARTMENT ORDER BY SALARY DESC) ASGROUP_NUMBER_IN_DEP_BY_SALARY

FROM EMP

DEPARTMENT ORDER BY SALARY DESC) ASGROUP_NUMBER_IN_DEP_BY_SALARY

FROM EMP

__Explanation:__
As you can see from the result set there are total 3 Departments, each department has 5 records in

it. All records in each department have been divided into 3 groups. Each group has it group number

with its records.

it. All records in each department have been divided into 3 groups. Each group has it group number

with its records.

Thanks for your update, Please Visit for Bank Exam Preparation -http://www.a4ambitionz.in

ReplyDeleteFind Bank Exam Preparation questions solution Here.- www.a4ambitionz.in