Pages

Social Icons

Tuesday 22 November 2011

Ranking Function

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

No comments:

Post a Comment