We have four types of ranking function in sql server
1.) row_number
2.) rank
3.) dense_rank
Row_Number() Function
The ROW_NUMBER() function generates the auto incrementing integer number to the sort order of the OVER() clause
it is used like this :
SELECT
ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNumber
,SalesOrderID
,Sales
FROM
SalesOrder
Rank() and Dense_rank() Function
RANK() and DENSE_RANK() function return values as if the rows were computing according to the sortorder.
For example if two record have the same sortorder value then both have same rank value. but next value in rank function will be +2 and in denseRank function it will be +1.
it is used like this :
SELECT
RANK() OVER(ORDER BY SalesOrderID) AS RANK
DENSE_RANK() OVER(ORDER BY SalesOrderID) AS DENSE_RANK
,SalesOrderID
,Sales
FROM
SalesOrder
1.) row_number
2.) rank
3.) dense_rank
Row_Number() Function
The ROW_NUMBER() function generates the auto incrementing integer number to the sort order of the OVER() clause
it is used like this :
SELECT
ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNumber
,SalesOrderID
,Sales
FROM
SalesOrder
Rank() and Dense_rank() Function
RANK() and DENSE_RANK() function return values as if the rows were computing according to the sortorder.
For example if two record have the same sortorder value then both have same rank value. but next value in rank function will be +2 and in denseRank function it will be +1.
it is used like this :
SELECT
RANK() OVER(ORDER BY SalesOrderID) AS RANK
DENSE_RANK() OVER(ORDER BY SalesOrderID) AS DENSE_RANK
,SalesOrderID
,Sales
FROM
SalesOrder
No comments:
Post a Comment