SQL Server Row_Number, Rank, Dense_Rank Functions Syntax and Examples

What is the Difference between Row_Number, Rank and Dense_Rank in SQL Server

Row_Number ()
This function Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER ( ) OVER ([<partition_by_clause>] <order_by_clause>)

Rank ( )
This function Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the rows in the criteria. 

RANK ( ) OVER ([< partition_by_clause >] < order_by_clause >)

Dense_Rank( )

This function Returns the rank of rows within the partition of a result set, without any gaps in the ranking order. The rank of a row is one plus the number of distinct ranks that come before the row in the
criteria.

DENSE_RANK ( ) OVER ([<partition_by_clause> ] < order_by_clause > )

NTILE ( )

This function Distributes 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.

NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)

Where :
<partition_by_clause>
This Divides the result set produced by the From clause into partitions to which the Row_Number/ Rank/ Dense_Rank/ Ntile function is applied.

<order_by_clause>
This Determines the order in which the Row_Number/ Rank/ Dense_Rank/ Ntile values are applied to the rows in a partition.

Lets Consider the following Table on we apply these functions :

Example 1: Row_Number/Rank/Dense_Rank/NTile  with Order by Dept_Name :

SELECT [Emp_Id]
          ,[Emp_Name]
         ,[Dept_Name]
          ,[Basic_Sal]
          ,ROW_NUMBER() OVER ( ORDER BY Dept_Name) AS ROW_NUM
  ,RANK() OVER ( ORDER BY Dept_Name) AS RANK
  ,DENSE_RANK() OVER ( ORDER BY Dept_Name) AS DENSE_RANK
  ,NTILE(7) OVER ( ORDER BY Dept_Name) AS NTILE
  FROM [dbo].[Tbl_Emp_Dtls]

GO

Output :

Notes :

Here Row_Num( ) assigns the Row Numbers in sequential order for each row

RANK( ) assigns the same Rank Number 1 (6 times) for the Dept_Name "BI&A", next when the Dept_Name changes, it  starts the Rank Numbers as 6+1...

Dense_Rank( ) assigns the same Rank Number 1 (6 times) for the Dept_Name "BI&A", next when the Dept_Name changes, it starts the Rank Numbers as 2 without breaking the ranking order.

NTile(7 ) distributes the rows into a specified number(7 here) of groups.

Example 2: Row_Number/Rank/Dense_Rank/NTile  with Order by Dept_Name :

In the below example the Row_Number/Rank/Dense_Rank/NTile will work in the same way as above but they are Partitioned by Basic_Sal

He in this example though you use NTILE(7), it distributes the rows in an ordered partition into only 6 groups , as 7th row is not available in that partition.

SELECT [Emp_Id]
   ,[Emp_Name]
   ,[Dept_Name]
   ,[Basic_Sal]
   ,ROW_NUMBER() OVER ( PARTITION BY Dept_Name ORDER BY Basic_Sal) 
                AS    ROW_NUM
   ,RANK() OVER ( PARTITION BY Dept_Name ORDER BY Basic_Sal) AS RANK
   ,DENSE_RANK() OVER ( PARTITION BY Dept_Name ORDER BY Basic_Sal) 
                    AS  DENSE_RANK
  ,NTILE(7) OVER ( PARTITION BY Dept_Name ORDER BY Basic_Sal) AS NTILE
  FROM [dbo].[Tbl_Emp_Dtls]
GO

Output :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------