Ranking Functions in SQL Server

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

Rank FunctionsDescription
RANKIt will assign the rank number to each record present in a partition.
DENSE_RANKIt will assign the rank number to each record within a partition without skipping the rank numbers
NTILEThis rank function will assign the rank number to each record present in a partition.
ROW_NUMBERIt will assign the sequential rank 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

-- SQL RANK Functions
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.