You may have come across the following scenario when using the LAST_VALUE() function.
You want to find the first or last value loaded in a result set, therefore you use the FIRST_VALUE() and LAST_VALUE() functions like below:
You expect to get “A” for every record in the FirstValue column and “E” for every record in the LastValue column.
However you see the following output:
LAST_VALUE() on its own is implemented from the current row going back to the first row, for example:
- Row 1 – The only value in scope is A.
Hence, FIRST_VALUE() & LAST_VALUE() both return A. - Row 2 – The values now in scope are A & B.
Hence, FIRST_VALUE() returns A and LAST_VALUE() returns B. - Row 3 – The values now in scope are A, B, C.
Hence, FIRST_VALUE() returns A and LAST_VALUE() returns C. - And so on…
To return the actual last value, add the additional clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This ensures that for each row the LAST_VALUE() function looks at all rows; from the very first row to the very last row. Now, you have the following code (the original code with an extra column using the additional clause):
This gives you the following output, LastValue2 shows the true last value for the result set:
