Thursday, 16 February 2017

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 (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 (times) for the Dept_Name "BI&A", next when the Dept_Name changes, it  starts the Rank Numbers as 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





1 comment:

  1. I just couldn't go away your site before suggesting that I extremely enjoyed
    the usual info a person supply in your visitors?
    Is gonna be again steadily in order to check up
    on new posts

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts