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