CODEWITHSUNDEEP
sql-serverwindow-functions
Joe
Joe

Reputation: 5497

SQL Server LAST_VALUE and LEAD

As demonstrated here, the LAST_VALUE function in SQL Server need to have a frame specified (eg. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) or the default will be the first row through the current row. This does not seem to be the case for LEAD. Both functions are "forward looking" so is there any documented reason for what appears to be an inconsistency?

Everything is working as expected, I am just trying to gain a more solid understanding of functionality that I have been using for years.

Upvotes: 0

Views: 2146

Answers (1)

Thom A
Thom A

Reputation: 95830

The default window frame for LAST_VALUE is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, as a result if you want all the rows after included, you need to use a specified window fra,e. This is documented in LAST_VALUE (Transact-SQL) - Using FIRST_VALUE and LAST_VALUE in a computed expression:

The clause "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is required in this example for the non-zero values to be returned in the DifferenceFromLastQuarter column, as shown below. The default range is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".

As a result, without ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING then you would get the current row with LAST_VALUE as that is the last row in the default window frame.

LEAD, on the other hand, doesn't use ROWS or RANGE BETWEEN, so has access to the entire scope of the dataset. Trying to use ROWS BETWEEN will generate an error:

SELECT V.I,
       LEAD(V.I) OVER (ORDER BY V.I ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS L
FROM (VALUES(1),(2),(3))V(I);

The function 'LEAD' may not have a window frame.

Upvotes: 2

Related Questions