The Ranking functions in SQL Server return a ranking value for each row in a partition. Microsoft provides various 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.
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]
Comments are closed.