Thursday, March 27, 2008

Ranking Functions : ROW_NUMBER

One of the features that was included in SQL Server 2005 were ranking functions and I bumped into one of these last week, The ROW_NUMBER().
This function allows you to provide a sequential integer value to the results of your query. This comes in handy when you want to rank your data according to some criteria.
Let me throw an example…I have a table that has a set of student marls, I can use a query shown below to actually rank the students according to there marks.

SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Rank,
StudentName, Score
FROM Students
ORDER BY StudentName.

In the OVER() clause you can specify multiple order-by clauses.
This function also as its use when it comes to pagination of data. Take a look at the query below.

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, StundentName) AS rownum,
StudentName, Score
FROM Students) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, StudentName.

This query fetches the rows between 4 to 6.

You can find more about ranking functions here.

No comments:

Post a Comment