Ranking Functions in SQL Server 2012

Here, we will take a look at how to use Ranking Functions in SQL Server 2012. Ranking Functions are used to allow the sequential number of the result set or Ranking Functions return a ranking value for each row in a partition. So let's take a look at a practical example of how to use Ranking Functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio. There are four new Ranking Functions in SQL Server. Those functions are:
  1. ROW_NUMBER
  2. RANK
  3. DENSE_RANK
  4. NTILE
Now open a SQL Server Management Studio. Use Start -> All Programs -> Microsoft SQL Server 2012 -> Microsoft SQL Server Management Studio.
 

Creating a Table in SQL Server

 
The table looks as in the following:
 
Table-in-sqlserver.jpg
 

ROW_NUMBER() Function in SQL Server

 
The Row_Numaber function is an important function when you do paging in SQL Server. The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows. 
 
Syntax
 
ROW_NUMBER ()
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
 
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
 
The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.
 
Row_Number() Example
  1. SELECT *, ROW_NUMBER() OVER(ORDER BY User_Id) AS Row_Number  
  2. FROM userdetail  
OUTPUT
 
ROW_NUMBER Function in SQL Server
 

Rank() Function in SQL Server

 
The Rank function produces a rank based on the sorted order when two rows have the same order value. It provides the same rank for the two rows and also increases the same order by clause.
 
Syntax
 
RANK ( ) OVER ( [partition_by_clause] order_by_clause )
 
Rank() Example
  1. SELECT *,  RANK() OVER(ORDER BY User_Id) AS Rank_Number  
  2. FROM userdetail  
OUTPUT
 
Rank Function in SQL Server
 
In the preceding image two rows have the same order value then provides the same rank for the two rows.
 

DENSE_RANK() Function in SQL Server

 
The DENSE_RANK function produces a rank based on the sorted order without any gap. When two rows have the same order value, it provides the same rank for the two rows and also increases the next row value.
 
Syntax
 
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
 
Dense_Rank() Example
  1. SELECT *,  DENSE_RANK() OVER(ORDER BY User_Id) AS Dense_Number  
  2. FROM userdetail  
OUTPUT
 
Dense_Rank Function in SQL Server
 
In the preceding image two rows have the same order value then provides the same rank for the two rows and also increases the next row value. The difference between the Rank() function and the Dense_Rank() function is shown in the output tables of both functions.
 

NTILE() Function in SQL Server

 
Divides the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
 
Syntax
 
NTILE (int_exp) OVER ([<partition_by_clause>]<order_by_clause>)
 
Ntile() Example
  1. SELECT *, NTILE(5) OVER (ORDER BY User_Id) AS Ntile_Number  
  2. FROM userdetail  
OUTPUT
 
NTILE Function in SQL Server


Similar Articles