Post
Ask Question

Ranking Function In Microsoft SQL Server

Introduction
 
In this article, I will explain the use of the Ranking function in Microsoft SQL Server. In common use, Rank shows the position and courage of a person in a real life scenario. So here, I will also give some examples related to all Ranking functions in Microsoft SQL Server.
 
Table in Microsoft SQL Server
 
My table contains bulk amount of data and it’s related to student marks list. So, I am going to apply Microsoft SQL Server Ranking function in the following Table “Table_MarkList”.
 
 
 
SQL Query 
  1. USE [Test]  
  2. GO  
  3. /****** Object:  Table [dbo].[Table_MarkList]    Script Date: 08/03/2016 12:06:32 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. SET ANSI_PADDING ON  
  9. GO  
  10. CREATE TABLE [dbo].[Table_MarkList](  
  11.     [Id] [int] IDENTITY(1000,1) NOT NULL,  
  12.     [Name] [varchar](50) NULL,  
  13.     [Marks] [int] NULL  
  14. ) ON [PRIMARY]  
  15. GO  
  16. SET ANSI_PADDING OFF  
  17. GO  
  18. SET IDENTITY_INSERT [dbo].[Table_MarkList] ON  
  19. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1000, N'Vimal', 85)  
  20. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1001, N'Nikhil', 85)  
  21. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1002, N'Prasanth', 84)  
  22. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1003, N'Manaf', 83)  
  23. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1004, N'Naveen', 82)  
  24. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1005, N'Midhun', 80)  
  25. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1006, N'Vijeth', 95)  
  26. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1007, N'Dileep', 97)  
  27. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1008, N'Ashiq', 92)  
  28. INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1009, N'Rajeesh', 75)  
  29. 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 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.
 
 
 
RANK() function SQL Query
  1. 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. 
 
 
 
DENSE_RANK() function SQL Query
  1. 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. 
 
 
 
Row_Number() Function SQL Query
  1. 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 in  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 in 3 groups but the first group will have 1 extra row value than the other row values in a table.
 
 
 
NTILE() Function SQL Query
  1. select Name,Marks, NTILE(3) over(order by Marks desc) As 'Quartile' From dbo.Table_MarkList   
Reference 
Summary
 
We learned the types of Ranking functions in Microsoft SQL Server and I hope you liked this article. Please share your valuable suggestions and feedback. 


Similar Articles