RANKING using OVER Clause in TSQL

There are majorly 4 different ranking functions in SQL Server. They are –

  1. ROW_NUMBER()
  2. RANK()
  3. DENSE_RANK()
  4. NTILE(INT)

They all are different and in my own terminology they can be used to define row versions in programmatic point of view. I mean to say, when we want more precise control on every row of a group or partition, these functions will give us more flexibility. Let us first understand these functions –

ROW_NUMBER()
Returns the incremental number of a row within a partition of a result set, starting at 1 for the first row in each partition. When no partition is specified it treats all rows as single partition and gives each row different row number starting from 1.

RANK()
Returns the rank of each row within the partition of a result set. when partition was specified all the rows in a partition of same Order by clause would be ranked with the same number and next order by group will be getting rank from (Rank of previous Order by + number of rows in the previous Order by). When no partition specified, RANK() does take ORDER BY column and associates ranks using the same.

DENSE_RANK()
Returns the rank of rows within the partition of a result set, but unlike RANK() function, it returns continuous ranks for partitions without any exclusions. When no partition has been specified, then DENSE_RANK() takes the ORDER BY clause and
then associates continuous ranking to the rows. When partition by is specified it groups the rows by partition clause and then gives continuous ranks to the rows based on Order by clause, which is going to be continuous.

NTILE(INT)
Distributes the rows in an ordered partition into a specified number of groups. From MSDN, If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If
on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

 

Based on above understanding lets see some sample code –

/**************************** Create TestDB Database *****************************/ 

IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO

USE [TestDB]
GO


/**************************** Create Employee Table ******************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'EmployeeRoles')
BEGIN
    DROP TABLE [dbo].[EmployeeRoles];
END
GO

CREATE TABLE [dbo].[EmployeeRoles](
    [EmpRoleId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [EmpRole] [nvarchar](32) NOT NULL,
    [InsertedDate] [datetime] NOT NULL DEFAULT GETDATE()
)
GO


/************************ Enter Employee Roles Details *************************/

INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (1,'CEO');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (2,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (3,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (4,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (5,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (6,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (7,'CTO');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (8,'PM');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (9,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (10,'Sdet');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (11,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (12,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (13,'PM');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (14,'Test');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (15,'Dev');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole]) VALUES (16,'Test');
GO


/*********************** ROW_NUMBER() with PARTITION *************************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            ROW_NUMBER() 
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS RowNumberWithPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[RowNumberWithPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/********************** ROW_NUMBER() without PARTITION ***********************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            ROW_NUMBER() 
                OVER(ORDER BY [EmpId] ASC) AS RowNumberWithOutPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[RowNumberWithoutPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/*************************** RANK() with PARTITION ***************************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            RANK() 
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS RankNumberWithPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[RankNumberWithPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/************************* RANK() without PARTITION **************************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            RANK() 
                OVER(ORDER BY [EmpId] ASC) AS RankNumberWithoutPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[RankNumberWithoutPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/*********************** DENSE_RANK() with PARTITION *************************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            DENSE_RANK() 
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpId] ASC) AS DenseRankWithPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[DenseRankWithPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/********************** DENSE_RANK() without PARTITION ***********************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            DENSE_RANK() 
                OVER(ORDER BY [EmpRole] ASC) AS DenseRankWithoutPartition
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[DenseRankWithoutPartition]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/************************ NTILE(int) with PARTITION **************************
******************************************************************************/

WITH GetRowRanks AS 
(
     SELECT 
            [EmpId],
            [EmpRole],
            NTILE(4) 
                OVER(PARTITION BY [EmpRole] ORDER BY [EmpRole] ASC) AS NtileNumber
      FROM 
            [dbo].[EmployeeRoles]
)
SELECT 
       GRR.[EmpId], 
       GRR.[EmpRole], 
       GRR.[NtileNumber]
FROM 
       GetRowRanks GRR
ORDER BY
       GRR.[EmpRole] ASC;
GO


/**************************** Drop TestDB Database *****************************/

USE master
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

 

OUTPUT –

 

EmpId       EmpRole                          RowNumberWithPartition
----------- -------------------------------- ----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          RowNumberWithoutPartition
----------- -------------------------------- -------------------------
1           CEO                              1
7           CTO                              7
9           Dev                              9
2           Dev                              2
4           Dev                              4
11          Dev                              11
15          Dev                              15
13          PM                               13
8           PM                               8
10          Sdet                             10
3           Sdet                             3
6           Sdet                             6
16          Test                             16
14          Test                             14
12          Test                             12
5           Test                             5

(16 row(s) affected)

EmpId       EmpRole                          RankNumberWithPartition
----------- -------------------------------- -----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          RankNumberWithoutPartition
----------- -------------------------------- --------------------------
1           CEO                              1
7           CTO                              7
9           Dev                              9
2           Dev                              2
4           Dev                              4
11          Dev                              11
15          Dev                              15
13          PM                               13
8           PM                               8
10          Sdet                             10
3           Sdet                             3
6           Sdet                             6
16          Test                             16
14          Test                             14
12          Test                             12
5           Test                             5

(16 row(s) affected)

EmpId       EmpRole                          DenseRankWithPartition
----------- -------------------------------- ----------------------
1           CEO                              1
7           CTO                              1
2           Dev                              1
4           Dev                              2
9           Dev                              3
11          Dev                              4
15          Dev                              5
8           PM                               1
13          PM                               2
3           Sdet                             1
6           Sdet                             2
10          Sdet                             3
5           Test                             1
12          Test                             2
14          Test                             3
16          Test                             4

(16 row(s) affected)

EmpId       EmpRole                          DenseRankWithoutPartition
----------- -------------------------------- -------------------------
1           CEO                              1
7           CTO                              2
9           Dev                              3
2           Dev                              3
4           Dev                              3
11          Dev                              3
15          Dev                              3
13          PM                               4
8           PM                               4
10          Sdet                             5
3           Sdet                             5
6           Sdet                             5
16          Test                             6
14          Test                             6
12          Test                             6
5           Test                             6

(16 row(s) affected)

EmpId       EmpRole                          NtileNumber
----------- -------------------------------- --------------------
1           CEO                              1
7           CTO                              1
9           Dev                              1
2           Dev                              1
4           Dev                              2
11          Dev                              3
15          Dev                              4
13          PM                               1
8           PM                               2
10          Sdet                             1
3           Sdet                             2
6           Sdet                             3
16          Test                             1
14          Test                             2
12          Test                             3
5           Test                             4

(16 row(s) affected)

You may also like...