What is Ranking Function?
What are different Ranking Functions in SQL Server?
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.
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.
CREATE DATABASE DATABASENAME
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.
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
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.
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.
Syntax:
SELECT EMPNAME, DEPARTMENT, SALARY , RANK() OVER(PARTITION BY DEPARTMENT ORDER BY SALARY DESC) ASRANK_IN_DEP_BY_SALARY FROM EMP
Output:
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.
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.
Syntax:
You want to get each employee rank without any gap in their department based on their highest salary.
Solution:
You can use DENSE_RANK function with PARTITION BY Clause on DEPARTMENT and 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
Output:
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.
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.
Syntax:
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.
Problem 2:
You want divide each employee in their department based on their highest salary into 2 groups.
Solution:
You can use NTILE function with INTEGER EXPRESSION value equal to 2, PARTITION BY Clause on DEPARTMENT 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
Output:
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.
#################################################################################
Example - 2
################################################################################
Let's take following sample table and data to know about RANK, RANK_DENSE, NTILE and ROW_NUMBER with examples.
Create table ExamResult(name varchar(50),Subject varchar(20),Marks int) insert into ExamResult values('Adam','Maths',70) insert into ExamResult values ('Adam','Science',80) insert into ExamResult values ('Adam','Social',60) insert into ExamResult values('Rak','Maths',60) insert into ExamResult values ('Rak','Science',50) insert into ExamResult values ('Rak','Social',70) insert into ExamResult values('Sam','Maths',90) insert into ExamResult values ('Sam','Science',90) insert into ExamResult values ('Sam','Social',80)
RANK()
Returns the rank of each row in the result set of partitioned column.select Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From ExamResult order by name,subject
DENSE_RANK()
This is same as RANK() function. Only difference is returns rank without gaps.select Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From
ExamResult order by name
In RANK() result set screenshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().
NTILE()
Distributes the rows in an ordered partition into a specified number of groups.
It divides the partitioned result set into specified number of groups in an order.
Example for NTILE(2)select Name,Subject,Marks, NTILE(2) over(partition by name order by Marks desc) Quartile From ExamResult order by name,subject
.JPG&container=blogger&gadget=a&rewriteMime=image%2F*)
Example for NTILE(3)select Name,Subject,Marks, NTILE(3) over(partition by name order by Marks desc) Quartile From ExamResult order by name,subject
.JPG&container=blogger&gadget=a&rewriteMime=image%2F*)
ROW_NUMBER()
Returns the serial number of the row order by specified column.select Name,Subject,Marks, ROW_NUMBER() over(order by Name) RowNumber From ExamResult order by name,subject
#################################################################################
Example - 3
################################################################################
DENSE_RANK
The DENSE_RANK Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition without skipping the rank numbers.
NOTE: In SQL, If the DENSE_RANK function encounters two equal values in the same partition, it will assign the same rank number to both values.
SQL DENSE_RANK Function Syntax
In SQL Server, The basic syntax of the DENSE_RANK Function is:
SELECT DENSE_RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, DENSE_RANK Function will assign the rank number to each partition.
If you havent specified the Partition By Clause then, DENSE_RANK Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please referSQL Order By Clause for better understanding.
In this article we will show you, How to write DENSE_RANK Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL DENSE_RANK Function Example
DENSE_RANK Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used DENSE_RANK() Function with Partition by clause so, DENSE_RANK function will assign the rank numbers for each individual partition.
DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 3 and 4 records because their yearly income is same. Next, it assigned 2nd rank to next record (it wont skip any rank numbers).
SQL DENSE_RANK Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in DENSE_RANK () Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,DENSE_RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used DENSE_RANK() Function without Partition by clause so, DENSE_RANK function will consider them as single partition and assign the rank numbers from begining to end.
DENSE_RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 2, 3 and 4 records because their yearly income is same (80000). Next, it has given same rank to 5 and 6 records because their yearly income is same and so on.
SQL DENSE_RANK Function On String Column
DENSE_RANK Function also allows you to rank the string columns. In this example, We are going to use the DENSE_RANK() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS RANK
FROM [Customers]
OUTPUT
If you observe the above screenshot, DENSE_RANK Function has given same rank to 6 and 7 records because their First name is same. Next, it assigned 2nd rank to next record.
NTILE
The NTILE Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition.
SQL NTILE Function Syntax
In SQL Server, The basic syntax of the NTILE Function is:
SELECT NTILE(Interger_Value) OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Integer_Value: NTILE Function will use this integer value to decide, the number of ranks it has to assign for each partition. For instance, If we specify 2, NTILE Function will assign 2 rank numbers for each partition.
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, NTILE Function will assign the rank number to each partition.
If you havent specified the Partition By Clause then, NTILE Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please referSQL Order By Clause for better understanding.
In this article we will show you, How to write NTILE Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL NTILE Function Example 1
NTILE Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used NTILE(2) Function with Partition by clause. Here integer value is 2, It means NTILE Function will assign two rank number for each individual partition (4 partitions).
NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
Although the yearly income of is not same for 6 and 7 records (90000 and 80000), It has given same rank to them because NTILE(2) can assign only 2 two ranks for a partition.
SQL NTILE Function Example 2
In this example we will show you, What will happen if we assign different value atInteger_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,NTILE(3) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
Above query will assign three rank number per partition.
OUTPUT
Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because NTILE(3) will assign 3 ranks for a partition.
SQL NTILE Function without Partition By Clause 1
In this example we will show you, What will happen if we miss the Partition By Clause in NTILE() Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,NTILE(2) OVER (
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used NTILE(2) Function without Partition by clause. Here integer value is 2, It means NTILE Function will assign two rank number for each individual partition.
Since we havent used any partition, NTILE Function will consider all the 10 records as single partition. Next, It will assign 2 rank number to that 10 records.
NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
SQL NTILE Function without Partition By Clause 2
In this example we will show you, What will happen if we assign different value at Integer_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,NTILE(3) OVER (
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
Here we haven’t used any partition, NTILE Function will consider all the 10 records as single partition. Next, It will assign 3 rank number to that 10 records because we have givenInteger_Value as 3.
RANK
The RANK Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition.
NOTE: In SQL, If the RANK function encounters two equal values in the same partition, it will assign the same rank number to both values and skips the next number in the ranking.
SQL RANK Function Syntax
In SQL Server, The basic syntax of the RANK Function is:
SELECT RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, RANK Function will assign the rank number to each partition.
If you haven't specified the Partition By Clause then, RANK Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.
In this article we will show you, How to write RANK Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL RANK Function Example
RANK Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used RANK() Function with Partition by clause so, Rank function will assign the rank numbers for each individual partition.
RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 3 and 4 records because their yearly income is same. Next, it skipped one rank and assigned 3rd rank to next record.
SQL RANK Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in RANK () Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used RANK() Function without Partition by clause so, Rank function will consider them as single partition and assign the rank numbers from begining to end.
RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 2, 3 and 4 records because their yearly income is same. Next, it skipped two ranks for those two records (3, 4) and assigned 5th rank to next record.
SQL RANK Function On String Column
RANK Function also allows you to rank the string columns. In this example, We are going to use the RANK() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS RANK
FROM [Customers]
OUTPUT
If you observe the above screenshot, RANK Function has given same rank to 6 and 7 records because their First name is same. Next, it skipped one rank and assigned 3rd rank to next record.
ROW_NUMBER
The ROW_NUMBER Function is one of the SQL Server ranking function. This function will assign the sequential rank number to each unique record present in a partition.
NOTE: In SQL, If the ROW_NUMBER function encounters two equal values in the same partition, it will assign the different rank number to both values. Here rank number will depends up on the order they displayed.
SQL ROW_NUMBER Function Syntax
In SQL Server, The basic syntax of the ROW_NUMBER Function is:
SELECT ROW_NUMBER() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, ROW_NUMBER Function will assign the rank number to each partition.
If you haven't specified the Partition By Clause then, ROW_NUMBER Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.
In this article we will show you, How to write ROW_NUMBER Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL ROW_NUMBER Function Example
ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used ROW_NUMBER() Function with Partition by clause so, ROW_NUMBER function will assign the rank numbers for each individual partition.
ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because ROW_NUMBER() assign unique ranks to each individual record.
SQL ROW_NUMBER Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in ROW_NUMBER() Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used ROW_NUMBER() Function without Partition by clause so, Rank function will consider them as single partition and assign the rank numbers from beginning to end.
ROW_NUMBER() OVER (
ORDER BY [YearlyIncome] DESC
) AS [ROW_NUMBER]
SQL ROW_NUMBER Function On String Column
ROW_NUMBER Function also allows you to rank the string columns. In this example, We are going to use the ROW_NUMBER() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
If you observe the above screenshot, Although the first name is same for 6 and 7 records (John), It has given different rank to them because ROW_NUMBER() assign unique ranks to each individual record.
#################################################################################
Example - 4
################################################################################
Ranking Function
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non-deterministic and return Big Integer values.
This article is related to RANKING Function.
ROW_NUMBER function
The ROW_NUMBER function returns the row number over a named or unnamed window specification.
The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. The LIMIT keyword used in other databases is not defined in the SQL standard, and is not supported.
Derby does not currently allow the named or unnamed window specification to be specified in the OVER() clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.
The ROW_NUMBER function cannot currently be used in a WHERE clause.
Derby does not currently support ORDER BY in sub queries, so there is currently no way to guarantee the order of rows in the SELECT sub query. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.
RANK() function
If two or more rows tie for a rank, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
The sort order that is used for the whole query determines the order in which the rows appear in a result set.
DENSE_RANK()function
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
NTILE() function
If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
Example are mentioned bellow
CREATE TABLE #EXMAPLE_TABLE
( CODE INT IDENTITY(1,1) NOT NULL,
SNAME VARCHAR(50) NOT NULL,
GRADE CHAR(1) NOT NULL)
GO
INSERT INTO #EXMAPLE_TABLE(SNAME, GRADE)
VALUES
('Raja', 'A'),
('Prja', 'B'),
('Saja', 'C'),
('Khaja', 'A'),
('Baja', 'B'),
('Balaram', 'C'),
('Majhi', 'B'),
('Sajal', 'B'),
('Sarita', 'C'),
('Sarika', 'C')
SELECT ROW_NUMBER() OVER(ORDER BY GRADE) [ROW_NUMBER],
RANK() OVER(ORDER BY GRADE) [RANK],
DENSE_RANK() OVER(ORDER BY GRADE) [DENSE_RANK],
NTILE(4) OVER(ORDER BY GRADE) [NTILE],
SNAME, GRADE
FROM #EXMAPLE_TABLE
ROW_NUMBER RANK DENSE_RANK NTILE SNAME GRADE
1 1 1 1 Raja A
2 1 1 1 Khaja A
3 3 2 1 Baja B
4 3 2 2 Prja B
5 3 2 2 Majhi B
6 3 2 2 Sajal B
7 7 3 3 Sarita C
8 7 3 3 Sarika C
9 7 3 4 Saja C
10 7 3 4 Balaram C
SELECT ROW_NUMBER() OVER(PARTITION BY GRADE ORDER BY GRADE)[ROW_NUMBER],
SNAME, GRADE
FROM #EXMAPLE_TABLE
ROW_NUMBER SNAME GRADE
1 Raja A
2 Khaja A
1 Baja B
2 Prja B
3 Majhi B
4 Sajal B
1 Sarita C
2 Sarika C
3 Saja C
4 Balaram C
What are different Ranking Functions in SQL Server?
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.
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.
CREATE DATABASE DATABASENAME
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.
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
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.
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.
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.
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.
Solution:
You can use RANK function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY , RANK() OVER(ORDER BY SALARY DESC) AS OVER_ALL_RANK_BY_SALARY FROM EMP
Output:
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.
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:
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.
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.
Solution:
You can use RANK function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY , RANK() OVER(ORDER BY SALARY DESC) AS OVER_ALL_RANK_BY_SALARY FROM EMP
Output:
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.
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
Output:
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.
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.
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.
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.
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.
Solution:
You can use DENSE_RANK function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY , DENSE_RANK() OVER(ORDER BY SALARY DESC) AS OVER_ALL_RANK_BY_SALARY FROMEMP
Output:
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.
Problem 2:
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.
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.
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.
Solution:
You can use DENSE_RANK function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY , DENSE_RANK() OVER(ORDER BY SALARY DESC) AS OVER_ALL_RANK_BY_SALARY FROMEMP
Output:
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.
Problem 2:
You want to get each employee rank without any gap in their department based on their highest salary.
Solution:
You can use DENSE_RANK function with PARTITION BY Clause on DEPARTMENT and 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
Output:
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.
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.
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.
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.
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.
Solution:
You can use ROW_NUMBER function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS ROW_NUMBER_BY_SALARY FROM EMP
Output:
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.
Problem 2:
You want to get serial number for each employee in their department based on their highest salary.
Solution:
You can use ROW_NUMBER function with PARTITION BY Clause on DEPARTMENT and ORDER 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
Output:
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.
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.
Syntax:
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.
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.
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.
Solution:
You can use ROW_NUMBER function without PARTITION By Clause and ORDER BY Clause on SALARY in Descending order.
SQL Code:
SELECT EMPNAME, DEPARTMENT, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS ROW_NUMBER_BY_SALARY FROM EMP
Output:
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.
Problem 2:
You want to get serial number for each employee in their department based on their highest salary.
Solution:
You can use ROW_NUMBER function with PARTITION BY Clause on DEPARTMENT and ORDER 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
Output:
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.
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.
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.
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.
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 NTILE function without PARTITION By Clause and with Inter_expression equals to 3 and 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
Output:
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.
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.
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 NTILE function without PARTITION By Clause and with Inter_expression equals to 3 and 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
Output:
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.
Problem 2:
You want divide each employee in their department based on their highest salary into 2 groups.
Solution:
You can use NTILE function with INTEGER EXPRESSION value equal to 2, PARTITION BY Clause on DEPARTMENT 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
Output:
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.
#################################################################################
Example - 2
################################################################################
Let's take following sample table and data to know about RANK, RANK_DENSE, NTILE and ROW_NUMBER with examples.
Create table ExamResult(name varchar(50),Subject varchar(20),Marks int) insert into ExamResult values('Adam','Maths',70) insert into ExamResult values ('Adam','Science',80) insert into ExamResult values ('Adam','Social',60) insert into ExamResult values('Rak','Maths',60) insert into ExamResult values ('Rak','Science',50) insert into ExamResult values ('Rak','Social',70) insert into ExamResult values('Sam','Maths',90) insert into ExamResult values ('Sam','Science',90) insert into ExamResult values ('Sam','Social',80)
RANK()
Returns the rank of each row in the result set of partitioned column.select Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From ExamResult order by name,subject
DENSE_RANK()
This is same as RANK() function. Only difference is returns rank without gaps.select Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From
ExamResult order by name
In RANK() result set screenshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().
NTILE()
Distributes the rows in an ordered partition into a specified number of groups.
It divides the partitioned result set into specified number of groups in an order.
Example for NTILE(2)select Name,Subject,Marks, NTILE(2) over(partition by name order by Marks desc) Quartile From ExamResult order by name,subject
Example for NTILE(3)select Name,Subject,Marks, NTILE(3) over(partition by name order by Marks desc) Quartile From ExamResult order by name,subject
ROW_NUMBER()
Returns the serial number of the row order by specified column.select Name,Subject,Marks, ROW_NUMBER() over(order by Name) RowNumber From ExamResult order by name,subject
#################################################################################
Example - 3
################################################################################
DENSE_RANK
The DENSE_RANK Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition without skipping the rank numbers.
NOTE: In SQL, If the DENSE_RANK function encounters two equal values in the same partition, it will assign the same rank number to both values.
SQL DENSE_RANK Function Syntax
In SQL Server, The basic syntax of the DENSE_RANK Function is:
SELECT DENSE_RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, DENSE_RANK Function will assign the rank number to each partition.
If you havent specified the Partition By Clause then, DENSE_RANK Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please referSQL Order By Clause for better understanding.
In this article we will show you, How to write DENSE_RANK Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL DENSE_RANK Function Example
DENSE_RANK Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used DENSE_RANK() Function with Partition by clause so, DENSE_RANK function will assign the rank numbers for each individual partition.
DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 3 and 4 records because their yearly income is same. Next, it assigned 2nd rank to next record (it wont skip any rank numbers).
SQL DENSE_RANK Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in DENSE_RANK () Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,DENSE_RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used DENSE_RANK() Function without Partition by clause so, DENSE_RANK function will consider them as single partition and assign the rank numbers from begining to end.
DENSE_RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 2, 3 and 4 records because their yearly income is same (80000). Next, it has given same rank to 5 and 6 records because their yearly income is same and so on.
SQL DENSE_RANK Function On String Column
DENSE_RANK Function also allows you to rank the string columns. In this example, We are going to use the DENSE_RANK() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,DENSE_RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS RANK
FROM [Customers]
OUTPUT
If you observe the above screenshot, DENSE_RANK Function has given same rank to 6 and 7 records because their First name is same. Next, it assigned 2nd rank to next record.
NTILE
The NTILE Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition.
SQL NTILE Function Syntax
In SQL Server, The basic syntax of the NTILE Function is:
SELECT NTILE(Interger_Value) OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Integer_Value: NTILE Function will use this integer value to decide, the number of ranks it has to assign for each partition. For instance, If we specify 2, NTILE Function will assign 2 rank numbers for each partition.
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, NTILE Function will assign the rank number to each partition.
If you havent specified the Partition By Clause then, NTILE Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please referSQL Order By Clause for better understanding.
In this article we will show you, How to write NTILE Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL NTILE Function Example 1
NTILE Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used NTILE(2) Function with Partition by clause. Here integer value is 2, It means NTILE Function will assign two rank number for each individual partition (4 partitions).
NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
Although the yearly income of is not same for 6 and 7 records (90000 and 80000), It has given same rank to them because NTILE(2) can assign only 2 two ranks for a partition.
SQL NTILE Function Example 2
In this example we will show you, What will happen if we assign different value atInteger_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,NTILE(3) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
Above query will assign three rank number per partition.
OUTPUT
Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because NTILE(3) will assign 3 ranks for a partition.
SQL NTILE Function without Partition By Clause 1
In this example we will show you, What will happen if we miss the Partition By Clause in NTILE() Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,NTILE(2) OVER (
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used NTILE(2) Function without Partition by clause. Here integer value is 2, It means NTILE Function will assign two rank number for each individual partition.
Since we havent used any partition, NTILE Function will consider all the 10 records as single partition. Next, It will assign 2 rank number to that 10 records.
NTILE(2) OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
SQL NTILE Function without Partition By Clause 2
In this example we will show you, What will happen if we assign different value at Integer_Value position. For instance, The following SQL Query will use the above example query but we changed the NTILE (2) to NTILE(3).
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,NTILE(3) OVER (
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers]
OUTPUT
Here we haven’t used any partition, NTILE Function will consider all the 10 records as single partition. Next, It will assign 3 rank number to that 10 records because we have givenInteger_Value as 3.
RANK
The RANK Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition.
NOTE: In SQL, If the RANK function encounters two equal values in the same partition, it will assign the same rank number to both values and skips the next number in the ranking.
SQL RANK Function Syntax
In SQL Server, The basic syntax of the RANK Function is:
SELECT RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, RANK Function will assign the rank number to each partition.
If you haven't specified the Partition By Clause then, RANK Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.
In this article we will show you, How to write RANK Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL RANK Function Example
RANK Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used RANK() Function with Partition by clause so, Rank function will assign the rank numbers for each individual partition.
RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 3 and 4 records because their yearly income is same. Next, it skipped one rank and assigned 3rd rank to next record.
SQL RANK Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in RANK () Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used RANK() Function without Partition by clause so, Rank function will consider them as single partition and assign the rank numbers from begining to end.
RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
It has given same rank to 2, 3 and 4 records because their yearly income is same. Next, it skipped two ranks for those two records (3, 4) and assigned 5th rank to next record.
SQL RANK Function On String Column
RANK Function also allows you to rank the string columns. In this example, We are going to use the RANK() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS RANK
FROM [Customers]
OUTPUT
If you observe the above screenshot, RANK Function has given same rank to 6 and 7 records because their First name is same. Next, it skipped one rank and assigned 3rd rank to next record.
ROW_NUMBER
The ROW_NUMBER Function is one of the SQL Server ranking function. This function will assign the sequential rank number to each unique record present in a partition.
NOTE: In SQL, If the ROW_NUMBER function encounters two equal values in the same partition, it will assign the different rank number to both values. Here rank number will depends up on the order they displayed.
SQL ROW_NUMBER Function Syntax
In SQL Server, The basic syntax of the ROW_NUMBER Function is:
SELECT ROW_NUMBER() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]
Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.
If you specified the Partition By Clause then, ROW_NUMBER Function will assign the rank number to each partition.
If you haven't specified the Partition By Clause then, ROW_NUMBER Function will consider all the records as single partition so, it will assign the rank numbers from top to bottom.
Order_By_Clause: This is used to sort the Partitioned data into specified order. Please refer SQL Order By Clause for better understanding.
In this article we will show you, How to write ROW_NUMBER Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL ROW_NUMBER Function Example
ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example we will show you, How to rank the partitioned records present in a table. The following SQL Query will partition the data by Occupation and assign the rank number using the yearly income.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions
PARTITION BY [Occupation]
Below statement will sort the partitioned data in the descending order using their [yearly income]
ORDER BY [YearlyIncome] DESC
In the below statement we used ROW_NUMBER() Function with Partition by clause so, ROW_NUMBER function will assign the rank numbers for each individual partition.
ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
Although the yearly income of is same for 3 and 4 records (80000), It has given different rank to them because ROW_NUMBER() assign unique ranks to each individual record.
SQL ROW_NUMBER Function without Partition By Clause
In this example we will show you, What will happen if we miss the Partition By Clause in ROW_NUMBER() Function. For instance, The following SQL Query will use the above example query without Partition by clause.
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
ANALYSIS
Below statement will sort the yearly income data in the descending order
ORDER BY [YearlyIncome] DESC
In the below statement we used ROW_NUMBER() Function without Partition by clause so, Rank function will consider them as single partition and assign the rank numbers from beginning to end.
ROW_NUMBER() OVER (
ORDER BY [YearlyIncome] DESC
) AS [ROW_NUMBER]
SQL ROW_NUMBER Function On String Column
ROW_NUMBER Function also allows you to rank the string columns. In this example, We are going to use the ROW_NUMBER() function to assign the rank numbers for [First name]
T-SQL CODE
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [FirstName] DESC
) AS [ROW NUMBER]
FROM [Customers]
OUTPUT
If you observe the above screenshot, Although the first name is same for 6 and 7 records (John), It has given different rank to them because ROW_NUMBER() assign unique ranks to each individual record.
#################################################################################
Example - 4
################################################################################
Ranking Function
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non-deterministic and return Big Integer values.
This article is related to RANKING Function.
ROW_NUMBER function
The ROW_NUMBER function returns the row number over a named or unnamed window specification.
The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. The LIMIT keyword used in other databases is not defined in the SQL standard, and is not supported.
Derby does not currently allow the named or unnamed window specification to be specified in the OVER() clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.
The ROW_NUMBER function cannot currently be used in a WHERE clause.
Derby does not currently support ORDER BY in sub queries, so there is currently no way to guarantee the order of rows in the SELECT sub query. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.
RANK() function
If two or more rows tie for a rank, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
The sort order that is used for the whole query determines the order in which the rows appear in a result set.
DENSE_RANK()function
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
NTILE() function
If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
Example are mentioned bellow
CREATE TABLE #EXMAPLE_TABLE
( CODE INT IDENTITY(1,1) NOT NULL,
SNAME VARCHAR(50) NOT NULL,
GRADE CHAR(1) NOT NULL)
GO
INSERT INTO #EXMAPLE_TABLE(SNAME, GRADE)
VALUES
('Raja', 'A'),
('Prja', 'B'),
('Saja', 'C'),
('Khaja', 'A'),
('Baja', 'B'),
('Balaram', 'C'),
('Majhi', 'B'),
('Sajal', 'B'),
('Sarita', 'C'),
('Sarika', 'C')
SELECT ROW_NUMBER() OVER(ORDER BY GRADE) [ROW_NUMBER],
RANK() OVER(ORDER BY GRADE) [RANK],
DENSE_RANK() OVER(ORDER BY GRADE) [DENSE_RANK],
NTILE(4) OVER(ORDER BY GRADE) [NTILE],
SNAME, GRADE
FROM #EXMAPLE_TABLE
ROW_NUMBER RANK DENSE_RANK NTILE SNAME GRADE
1 1 1 1 Raja A
2 1 1 1 Khaja A
3 3 2 1 Baja B
4 3 2 2 Prja B
5 3 2 2 Majhi B
6 3 2 2 Sajal B
7 7 3 3 Sarita C
8 7 3 3 Sarika C
9 7 3 4 Saja C
10 7 3 4 Balaram C
SELECT ROW_NUMBER() OVER(PARTITION BY GRADE ORDER BY GRADE)[ROW_NUMBER],
SNAME, GRADE
FROM #EXMAPLE_TABLE
ROW_NUMBER SNAME GRADE
1 Raja A
2 Khaja A
1 Baja B
2 Prja B
3 Majhi B
4 Sajal B
1 Sarita C
2 Sarika C
3 Saja C
4 Balaram C
