The Ranking functions in SQL Server returns ranking value for each row in a partition. Depending up on the function you select, return rank value will be different.
Ranking Functions in SQL Server
The SQL Server provides various Rank Functions, which allows us to assign different ranks. The following table will show you the list of available Ranking Functions in SQL Server
| Rank Functions in SQL Server | Description |
|---|---|
| RANK | It will assign the rank number to each record present in a partition. |
| DENSE_RANK | It will assign the rank number to each record present in a partition without skipping the rank numbers |
| NTILE | It will assign the rank number to each record present in a partition. |
| ROW_NUMBER | It will assign the sequential rank number to each unique record present in a partition. |
Ranking Functions in SQL Server example
In this example we are going to show you the functionality of all the rank functions in SQL server at one place
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 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] |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply