Chapter 13. Windowing and Ranking


  • Creating an independent sort of the result set

  • Grouping result sets

  • Calculating ranks, row numbers, and ntiles

Have you ever noticed the hidden arrow in the FedEx logo? Once you know that it's there, it's obvious, but in an informal poll of FedEx drivers, not one of them was aware of the arrow. Sometimes, just seeing things in a different perspective can help clarify the picture.

That's what SQL's windowing and ranking does—the windowing (using the over() clause) provides a new perspective on the data. The ranking functions then use that perspective to provide additional ways to manipulate the query results.

Windowing and ranking are similar to the last chapter's aggregate queries, but they belong in their own chapter because they work with an independent sort order separate from the query's order by clause, and should be thought of as a different technology than traditional aggregate queries.


Before the ranking functions can be applied to the query, the window must be established. Even though the SQL query syntax places these two steps together, logically it's easier to think through the window and then add the ranking function.

Referring back to the logical sequence of the query in Chapter 8, "Introducing Basic Query Flow," the OVER() clause occurs in the latter half of the logical flow of the query in step 6 after the column expressions and ORDER BY but before any verbs (OUTPUT, INSERT, UPDATE, DELETE, or UNION).

Get Microsoft® SQL Server® 2008 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.