SQL: Last_Value () returns an invalid result (but First_Value () works fine)

Question

I have a table in SQL Server 2012, as the snapshot shows:

Then I use Last_Value () and First Value to get AverageAmount of each EmpID for another YearMonth. The script looks like this:

SELECT A.EmpID, First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '200901AvgAmount', Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '201112AvgAmount' FROM Emp_Amt AS A 

However, the result for this query:

In the "201112AvgAmount" column, it shows different values ​​for each EmpID, while "200901AvgAmount" has the correct values.

Is there something wrong with my SQL script? I have done a lot of research on the Internet, but still can not find the answer ....

Answer 1

There is nothing wrong with your script, this is the way partitioning works on SQL Server: /. If you change LAST_VALUE to MAX, the result will be the same. Decision:

 SELECT A.EmpID, First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '200901AvgAmount', Last_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '201112AvgAmount' FROM Emp_Amt AS A 

There is a great post about this, link . GL!

Answer 2

Here is a quick query to illustrate the behavior:

 select v, -- FIRST_VALUE() and LAST_VALUE() first_value(v) over(order by v) f1, first_value(v) over(order by v rows between unbounded preceding and current row) f2, first_value(v) over(order by v rows between unbounded preceding and unbounded following) f3, last_value (v) over(order by v) l1, last_value (v) over(order by v rows between unbounded preceding and current row) l2, last_value (v) over(order by v rows between unbounded preceding and unbounded following) l3, -- For completeness' sake, let also compare the above with MAX() max (v) over() m1, max (v) over(order by v) m2, max (v) over(order by v rows between unbounded preceding and current row) m3, max (v) over(order by v rows between unbounded preceding and unbounded following) m4 from (values(1),(2),(3),(4)) t(v) 

The output of the above query can be seen here ( SQLFiddle here ):

 | V | F1 | F2 | F3 | L1 | L2 | L3 | M1 | M2 | M3 | M4 | |---|----|----|----|----|----|----|----|----|----|----| | 1 | 1 | 1 | 1 | 1 | 1 | 4 | 4 | 1 | 1 | 4 | | 2 | 1 | 1 | 1 | 2 | 2 | 4 | 4 | 2 | 2 | 4 | | 3 | 1 | 1 | 1 | 3 | 3 | 4 | 4 | 3 | 3 | 4 | | 4 | 1 | 1 | 1 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 

Few people think of implicit frames that apply to window functions that accept an ORDER BY . In this case, the windows default to the RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame. (RANGE is not exactly the same as ROWS, but that's a different story). Think of it this way:

• On a line with v = 1 ordered window frame spans v IN (1)
• On a line with v = 2 ordered window frame spans v IN (1, 2)
• On a line with v = 3 ordered window frame spans v IN (1, 2, 3)
• On a line with v = 4 ordered window frame spans v IN (1, 2, 3, 4)

If you want to prevent this behavior, you have two options:

• Use the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for ordered window functions
• Do not use the ORDER BY in these window functions that allow them to be excluded (like MAX(v) OVER() )

See LEAD() , LAG() , FIRST_VALUE() and LAST_VALUE()

Answer 3

The easiest way is to repeat your request using first_value, just make an order as asc for the 1st case and desc for the second case.

 SELECT A.EmpID, First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS '200901AvgAmount', First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey desc) AS '201112AvgAmount' FROM Emp_Amt AS A