An FAQ about the window clause in SQL Server Denali

I've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ.

Why does last_value not always "work right"?

Take, as an example, the following query (against Adventureworks2008R2)

select  AccountNumber,  SalesPersonID, SalesOrderID,
 first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct,
 last_value(SalesOrderID) over(partition by AccountNumber  order by SalesOrderID) as LastOrderForAcct
from Sales.SalesOrderHeader
where SalesPersonID IS NOT NULL

When you look as the results, FirstOrderForAcct seems OK, but LastOrderForAcct always returns the same number as the current account for each row. Not the last number. Why?

The reason is that, when using first/last_value with a window of rows, the default window is "range between unbounded preceding and current row". So the query above with the "weird" result is equivalent to this one.

select  AccountNumber,  SalesPersonID, SalesOrderID,
 first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
                                range between unbounded preceding and current row) as FirstOrderForAcct,
 last_value(SalesOrderID) over(partition by AccountNumber  order by SalesOrderID
                               range between unbounded preceding and current row) as LastOrderForAcct
from Sales.SalesOrderHeader
where SalesPersonID IS NOT NULL

If you want the "intuitive" answer for last_value, just change the window clause to:


select  AccountNumber,  SalesPersonID, SalesOrderID,
 first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
                                range between unbounded preceding and unbounded following) as FirstOrderForAcct,
 last_value(SalesOrderID) over(partition by AccountNumber  order by SalesOrderID
                               range between unbounded preceding and unbounded following) as LastOrderForAcct
from Sales.SalesOrderHeader
where SalesPersonID IS NOT NULL

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.