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