There are majorly 4 different ranking functions in SQL Server. They are –
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- 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)
