Ranking Functions in SQL Server

The Ranking functions in SQL Server return a ranking value for each row in a partition. Microsoft provides various Rank Functions, which 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

FunctionsDescription
RANKIt will assign the rank number to each record present in a partition.
DENSE_RANKIt will assign the number to each record within a partition without skipping the rank numbers
NTILEThis will assign the rank number to each record present in a partition.
ROW_NUMBERIt 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 at 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]
Ranking Functions in SQL Server

Comments are closed.