The Default Frame for Window Functions

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This bites me constantly, and I was reminded of this while watching Kathi talk at #SQLintheCity. When you write a Window function, there is an implicit default frame for the windows that you might not be aware of.

For example, if I have this data:

create table WindowDemo

( groupid int,

letterid int

, letter varchar(10))

GO

insert WindowDemo

values

( 1, 1, 'A')

, ( 1, 2, 'B')

, ( 1, 3, 'C')

, ( 2, 4, 'D')

, ( 2, 5, 'E')

GO

and I run this code:

select groupid
, letterid
, last_value(letter) over (partition by groupid order by letterid)
from WindowDemo

I get this:

2018-12-12 15_29_02-● SQLQuery3 - Azure Data Studio

Not what I expected. I would think the last value for each groupid is the largest letter. Instead,  I have a running total of sorts.

The Default Framing

There is a framing clause that I can use after the ORDER BY in the OVER clause. The default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW. At least, this is what appears when you include an ORDER BY clause. Many of us do this, but still get confused with the LAST_VALUE() and FIRST_VALUE functions.

What I really want is a complete set of data, which is either starting from the current row to the end, or  includes all values. If I modify my framing clause, I’ll get what I expect.

select groupid

, letterid

, last_value(letter) over (partition by groupid order by letterid rows between unbounded preceding and unbounded following)

from WindowDemo

This gives me:

2018-12-12 15_36_36-● SQLQuery3 - Azure Data Studio

That’s what I’d expect for a LAST_VALUE().

SQLNewBlogger

This has bitten me a few times, so I decided to write about it. I can show that I solved this issue, which is what my next boss wants to see. The other side effect is that blogging helps me remember how this works.

This took about 15 minutes, mostly to reproduce the demo that was similar to my issue, but simpler to explain.

About way0utwest

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

One Response to The Default Frame for Window Functions

  1. Pingback: Window Functions And Default Frames – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.