Ranking Function In Microsoft SQL Server

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.

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.

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.

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.

Reference

• Ranking Functions (Transact-SQL)
• TechBlog Content
  

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.