Remember the Default Window

I ran across a question recently from a user about why they had strange results from a windowing query. This is better explained with an example, so let’s look at one.

I have some data in a table. This is NFL data, and a sample of it looks like this:

2018-08-22 18_57_25-SQLQuery1.sql - Plato_SQL2016.NFLAnalysis (PLATO_Steve (52))_ - Microsoft SQL Se

What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:

  PlayerName  NFLYear PassYards Most Recent Yards Difference
------------- ------- --------- ----------------- -----------
Aaron Rodgers 2005 65 1675 -1610
Aaron Rodgers 2006 46 1675 -1629
Aaron Rodgers 2007 218 1675 -1457
Aaron Rodgers 2008 4038 1675 2363

This shows
me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.

In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.

2018-08-22 19_11_16-SQLQuery1.sql - Plato_SQL2016.NFLAnalysis (PLATO_Steve (52))_ - Microsoft SQL Se

It seems as though in every row, I’m getting the current row as the last value, not the last value of the partition. My partition is by player, so I should only have a window for each player. In this case, I should have the years 2005-2017 for Aaron Rodgers. My ordering is by year, so the last value should be 1675.

Why isn’t it?

The reason has to do with the framing. As the window is consumed, the default values for the framing are between

  • start – unbounded preceding
  • end – current row

That means the first row for 2005 has the range of 2005-2005. The preceding rows are this row, and the current row is this row. For 2006, we have the first row as 2005 and the current row as 2006. The last value in this case is 46.

What we need to do is specify the entire window if we want that. In this case, we could use the current row as the start, but we certainly need the unbounded following rows.

2018-08-22 19_18_30-SQLQuery1.sql - Plato_SQL2016.NFLAnalysis (PLATO_Steve (52))_ - Microsoft SQL Se

This is a common mistake when writing window queries. I’d recommend you always include the partition and the framing to avoid any issues.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

5 Responses to Remember the Default Window

  1. Pingback: Window Functions Have Defaults, Too – Curated SQL

  2. Eugene says:

    I have answer for a question on StackOverflow (https://stackoverflow.com/questions/51846536/islands-and-gaps-issue/51890263#51890263)where where OP has the same need – get last value. In his case LAST_VALUE was a perfect solution, so I explained him, as you did, that it’s crucial to understand how to correctly use LAST_VALUE. I offered him an example and solution. So, thanks for this article! I hope this will help other people to avoid mistakes. By the way, I saw this solution in TechEd 2012 conference called “Practical Uses and Optimisation of New T-SQL Features in Microsoft SQL Server 2012” by Tobias Ternstrom.

  3. way0utwest says:

    You are welcome, and plenty of people forget about the framing with window functions. I’ve learned and forgotten this many times.

  4. mike good says:

    Great article. Thank you.

Comments are closed.