You've Got Framing

The framing clause brings the "window" to window functions. It's an ideal solution when you can arrange a business question such that an answer comes from applying an aggregate function to a range of rows that slides or stretches smoothly as focus moves from one row to the next. Running sums and moving averages are just two examples of such problems. 

Read More
Jonathan Gennick
Pulling Rank for the Boss

Look to ROW_NUMBER, RANK, and DENSE_RANK anytime you are faced with a business question involving words or phrases such as "topmost" or "bottommost", "top N" or "bottom N", or that is otherwise answerable by ranking the rows in a result set according to some criteria that you can apply to one or more columns of data.

Read More
Jonathan Gennick
Interlude

Having the right mental model of how window functions operate goes a long way toward recognizing when and how to apply these functions that can seem almost magical in the expressive power.

Read More
Jonathan Gennick
Detail in the Aggregate

SQL Server 2012 – the "Denali" release – brings improved support for window functions as defined in the SQL standard. One of the things you can now do is to mix summary and detail results in the same row. 

Read More
Jonathan Gennick