As like an Aggregate functions, the Ranking functions will rank the values of specified field and categorize them according to their rank . Ranking functions are most commonly used to find the top records based on conditions, example, to find the top ranking students, top highest paid employees and etc.
There are four types of ranking functions in SQL Server as,
For this, lets take [Details].[Products] table as an example, and its result is as,
This function is used to give unique rank to specific records. If the record has same value, then RANK() function will assign same rank and skip the next rank. That is, if there are two identical values at rank 2, it will assign the same rank 2 to both records and then skip rank 3 and assign rank 4 to the next record.
SELECT ProductName, Rate, RANK() OVER(ORDER BY Rate DESC)[Rank] FROM [Details].[Products]
The DENSE_RANK() function is similar to the RANK() function except that it does not skip any rank. That is, if two identical records are found then DENSE_RANK() will assign the same rank to both records but not skip then skip the next rank.
SELECT ProductName, Rate, DENSE_RANK() OVER(ORDER BY Rate DESC)[Rank] FROM [Details].[Products]
These functions assign a unique row number to each record.
SELECT ProductName, Rate, ROW_NUMBER() OVER(ORDER BY Rate DESC)[Row Number] FROM [Details].[Products]
This function will identify what percentile a given row falls into. That is, if we have 100 rows and we want to create 4 quartiles based on a specified value field we can do so easily and see how many rows fall into each quartile
SELECT ProductName, Rate, NTILE(4) OVER(ORDER BY Rate DESC)[Rank] FROM [Details].[Products]
In this article, we have discussed about the ranking windows functions in SQL Server. I hope you all found this article much useful. We will discuss more concepts in our upcoming articles. Please share your feedbacks in the comment section.