Ranking Functions in SQL Server

The Ranking functions in SQL Server return a ranking value for each row in a partition. Microsoft provides various Functions that allow us to assign different ranks. Depending on the function you select, they return a different number. The following table will show you the list of available Ranking Functions.

Ranking Functions	Description
RANK	It will assign the rank number to each record present in a partition.
DENSE_RANK	It will assign the number to each record within a partition without skipping the numbers.
NTILE	This will assign the number to each record present in a partition.
ROW_NUMBER	It will assign the sequential number to each unique record present in a partition.

Ranking Functions in SQL Server Example

In this Server example, we are going to show you the functionality of all the rank functions in one place.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,RANK() OVER (ORDER BY [YearlyIncome] DESC) AS RANK
      ,DENSE_RANK() OVER (ORDER BY [YearlyIncome] DESC) AS [DENSE_RANK]
      ,ROW_NUMBER() OVER (ORDER BY [YearlyIncome] DESC) AS [ROW NUMBER]
      ,NTILE(3) OVER (ORDER BY [YearlyIncome] DESC) AS [NTILE NUMBER]
  FROM [Customers]