Ranking Functions in SQL

Ranking functions are used  for statistical ordering. In this Article, you learn how to use the four ranking functions available in the SQL Server 2008.

The four ranking functions are:-

1.    ROW_NUMBER

2.    RANK

3.    DENSE_RANK

4.    NTILE

1.    ROW_NUMBER

ROW_NUMBER assigns a sequential number from 1 to n to the rows based on user specified sorting order.

Syntax:

ROW_NUMBER() OVER (ORDER BY <column Name>)

Example :

Consider a table tblStudent with columns ‘Name’ and ‘Marks’.

Using ROW_NUMBER() function

SELECT Name,

                Marks,

                ROW_NUMBER()OVER (ORDER BY Marks) AS RowNo

FROM tblStudent

RESULT:

2.    RANK

This function does much the same thing as ROW_NUMBER.But if there are same values in the record, it is said to be a tie and these records get the same rank.

Syntax:

RANK() OVER (ORDER BY <columnName>)

Example :

Consider the same table tblStudent.

Using Rank function

SELECT Name,

                Marks,    

                RANK() OVER (ORDER BY Marks) AS RankNo

FROM tblStudent

RESULT :

3.    DENSE_RANK

DENSE_RANK assigns same value to the each duplicate records, but does not produce gaps in the sequence.

Syntax:

         DENSE_RANK() OVER (ORDER BY <columnName>)

Example :

Consider the same table tblStudent.

Using Dense_Rank function

   SELECT Name,

                Marks,    

                DENSE_RANK() OVER (ORDER BY Marks) AS DenseRankNo

FROM tblStudent

RESULT:

4.    NTILE

NTILE is used to split the result into approximately equal groups.If you want a result to split into 4 equal groups,you can use NTILE(4).

Syntax :

NTILE(integerExpression) OVER (ORDER BY <columnName>)

Example :

Consider the same table tblStudent.

Using NTILE Function

Reference : http://msdn.microsoft.com/en-us/library/ms189798(v=SQL.100).aspx