Thursday, February 16, 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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog