Microsoft® SQL Server® 2008 Bible

Chapter 13. Windowing and Ranking

IN THIS CHAPTER

• 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.

Windowing

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).

What's New with ...