Share via

SQL Server: Ranking Functions

  • Article

**Note: This Article Participated in **TechNet Guru Competition December 2016 Jump and won Silver Medal.

Introduction

In this article, we will explain the use of Ranking functions in Microsoft SQL Server. Ranking functions are the type of Window functions. In common use, Rank shows the position and courage of a person in a real-life scenario. So here, we will also give some examples related to all Ranking functions in Microsoft SQL Server.

Preparation

Our table contains a bulk amount of data and it's related to student marks list. So, We are going to apply Microsoft SQL Server Ranking function in the following Table "Table_MarkList".

DDL+DML

USE [Test] 
GO 
/****** Object:  Table  [dbo].[Table_MarkList]    Script Date: 08/03/2016 12:06:32 ******/ 
SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO 
SET ANSI_PADDING ON
GO 
CREATE TABLE  [dbo].[Table_MarkList]( 
    [Id] [int] IDENTITY(1000,1) NOT NULL, 
    [Name] [varchar](50) NULL,  
    [Marks] [int] NULL
) ON  [PRIMARY] 
GO 
SET ANSI_PADDING OFF
GO 
SET IDENTITY_INSERT [dbo].[Table_MarkList] ON
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1000, N'Vimal', 85) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1001, N'Nikhil', 85) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1002, N'Prasanth', 84) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1003, N'Manaf', 83) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1004, N'Naveen', 82) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1005, N'Midhun', 80) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1006, N'Vijeth', 95) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1007, N'Dileep', 97) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1008, N'Ashiq', 92) 
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES  (1009, N'Rajeesh', 75) 
SET IDENTITY_INSERT [dbo].[Table_MarkList] OFF

Ranking function in Microsoft SQL Server

The following are the Ranking functions in Microsoft SQL Server.

  • RANK()
  • DENSE_RANK()
  • Row_Number()
  • NTILE()

RANK()

One of the important Ranking functions in Microsoft SQL Server is RANK(). Rank() Ranking functions are non-deterministic and return a ranking value for each row in a partition. Some rows return the same rank as other rows, depending on the same values in columns.

The following result will be displayed after the RANK() function is applied in "Table_MarkList". So, the RANK() function is sorting the mark's column values in descending order, in the following table. The Rank() function will apply the same rank for common values in the table. The following table contains the same marks for "Vimal" & "Nikhil" and after applying Rank() function, it shows both of them with the same rank as "4". So, the next rank will show as "6" because internally it counts one 4 as 5.

select Name,Marks, RANK() over(order by  Marks desc) As  'Rank' From  dbo.Table_MarkList

DENSE_RANK()

Another important Ranking function in Microsoft SQL Server is DENSE_RANK(). It differs from RANK() function and it also returns the same rank as other rows, depending on the same values in columns. If the same rank happens, then the next rank will be +1 added to the last rank. 

The DENSE_RANK() function sorts the Marks column values in descending order in the following table "Table_MarkList". The DENSE_RANK() function will apply the same rank for common values in the table "Table_MarkList". So, the following table contains same marks for "Vimal" & "Nikhil", after applying DENSE_RANK() function, it has assigned these both the same rank as "4". So, the next rank will start from "5". It will maintain the rank order of the RANK() function. 

select Name,Marks, DENSE_RANK() over(order by  Marks desc) As  'Dense Rank' From  dbo.Table_MarkList

Row_Number()

One of the important Ranking functions in Microsoft SQL Server is Row_Number(). We can use this Ranking function for pagination purposes.

The Row_Number() function is sorting the Marks column values in descending order in the following table "Table_MarkList". The Row_Number() function will apply the same rank for common values in the table "Table_MarkList". The following table contains the same marks for "Vimal" & "Nikhil". After applying Row_Number() function, it is showing them both the rank as "4 & 5". So, it gives the result as a sequence, one by one,  and it never minds the common values in the "Marks" Column. Now the results are showing it accurately maintaining the Row Number in "Table_MarkList" table. 

select Name,Marks, Row_Number() over(order by  Marks desc) As  'Row Number' From  dbo.Table_MarkList

NTILE()

The important Ranking function in Microsoft SQL Server is NTILE(). We can use this Ranking function for grouping purposes.

NTILE(3) makes 3 groups and splits the number of row values into 3 equal groups. For example - If there are 9 row values in a table, it will split them into 3 equal row values in a table.

Simple! But what will happen on 10 row values in NTILE(3) ?. It's so simple. NTILE() will split them too into 3 groups but the first group will have 1 extra row value than the other row values in a table.

select Name,Marks, NTILE(3) over(order by  Marks desc) As  'Quartile' From  dbo.Table_MarkList

Back to Top

Reference

Conclusion

We learned the types of Ranking functions in Microsoft SQL Server and hope you liked this article. Please share your valuable suggestions and feedback.