Ranking Functions in SQL Server

ROW_NUMBER()  RANK()  DENSE_RANK()

SQL Server 2005 provides you easy and efficient ways for different types of ranking requirements. There are four ranking functions in SQL Server 2005:

RANK
DENSE_RANK
ROW_NUMBER
NTILE

NTILE is actually little bit different and it is used to assign a so called batch numbers or group numbers to the given result.

The general syntax for any one of these commands is more or less the same:

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)

 

RANK()      OVER ([<partition_by_clause>] <order_by_clause>)

 

DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>)

The PARTITION BY part is optional, but everything else is required.

ROW_NUMBER()

Assigns sequential numbers to each partition in a result set (an unpartitioned result set simply has a single partition), based upon the order of the results as specified in the ORDER BY clause.
If you look carefully, you'll see that the values in column ROWNO are based upon a simple sort of Branches.

RANK()

This function does much the same thing as ROW_NUMBER(), only it acknowledges ties in the columns specified in the ORDER BY clause, and assigns them the same rank. Where a tie occurs, the numbers that would otherwise have been "used up" are skipped, and numbering resumes at the next available number. As you can see, RANK() leaves a gap whenever there is a tie.

DENSE_RANK()

This function doesn't like gaps and doesn't leave any rank if tie occures. DENSE_RANK() "fills in the gaps". It starts from the next number after a tie occurs, so instead of 1, 2, 3, 3, 5 you get 1, 2, 3, 3, 4 for example.

Example:

SELECT      NAMEOFTHEBANK,

            Branches,

            RANK() OVER(ORDER BY Branches DESC) AS RANKID,

            ROW_NUMBER() OVER(ORDER BY Branches DESC) AS ROWNO,

            DENSE_RANK() OVER(ORDER BY Branches DESC) AS DENSERANK,

            NTILE(3) OVER(ORDER BY Branches DESC) AS NT

FROM tbl_Banks

GROUP BY NAMEOFTHEBANK

Output