Ranking Functions in sql server
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.
SQL DENSE_RANK Function Syntax
SELECT DENSE_RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Dtabase]
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.
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]
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,DENSE_RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
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.
In SQL Server, The basic syntax of the NTILE Function is:
1
2
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.
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]
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
USE [SQL Server Tutorials]
GO
SELECT [LastName]
,[Education]
,[YearlyIncome]
,[Occupation]
,[FirstName]
,NTILE(2) OVER (
ORDER BY [YearlyIncome] DESC
) AS [NTILE NUMBER]
FROM [Customers
RANK Function
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.
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 havent 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 referSQL Order By Clause for better understanding.
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
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
1
2
3
4
5
6
7
8
9
10
11
USE [SQL Server Tutorials]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,RANK() OVER (
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
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.
In SQL Server, The basic syntax of the ROW_NUMBER Function
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 havent 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 referSQL Order By Clause for better understanding.
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]
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 havent 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 referSQL Order By Clause for better understanding.