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,
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.
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, StundentName) AS rownum,
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.