Microsoft® SQL Server 2008 R2 Unleashed

The ROW_NUMBER Function

The ROW_NUMBER function allows you to provide sequential integer values to the result rows of a query, based on the order of the rows in the result. The result set must be ordered using an OVER clause, with an ORDER BY clause as a variable.

The ROW_NUMBER function has been a feature long desired by SQL Server developers. For example, suppose you want to return the publishers and total number of titles per publisher and list the result rows, in descending order, with a numeric score assigned to each row. The query shown in Listing 43.44 generates the desired results by using the ROW_NUMBER function, specifying ordering over the num_titles column, in descending order.

Listing 43.44 Using ROW_NUMBER to Rank Publishers by ...