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
