SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...