5

While testing my answer to SQL - Getting data from first and last row of each group I noticed something strange. The first_value and last_value window functions appear to behave differently.

As expected, first_value(col1) over (order by col2) finds the value of col1 from the row with the minimum value for col2. But last_value(col1) over (order by col2) seems to find the value for the current row. last_value seems unrelated to the group or partition it's operating on.

For the query below:

id  Session ID  bal
0   00000002    100
1   00000002    120
2   00000002    140
3   00000001    900
4   00000001    800
5   00000001    500

The expression:

last_value(bal) over (partition by [Session ID] order by id) as lv

Returns something different than:

first_value(bal) over (partition by [Session ID] order by id DESC) as fv_desc

Here's the result (note that the value of lv changes within one group):

lv       fv_desc
500      500
800      500
900      500
140      140
120      140
100      140

Example at SQL Fiddle with some extra columns. Reversing the order by or omitting partition by doesn't seem to impact what last_value() returns.

If I read the MSDN page correctly, it suggests that last_value() should work as the opposite of first_value(), which is different from what I observe during testing.

Does last_value() do what it is supposed to do? Why does last_value() allow you to specify a partition and/or order by clause when it doesn't seem to use either?

7

This happens because default window frame is range between unbounded preceding and current row, so the last_value() never looks beyond current row unless you change the frame.

From MSDN:

If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

Since last_value() has an optional order by clause, its default window frame ends at the current row. So with the default frame, no matter which partition or ordering you choose, last_value() returns the value from the current row.

  • ok, but "Why does last_value() allow you to specify a partition and/or order by clause when it doesn't seem to use either?" – Leo Sep 16 '14 at 15:45
  • I think it will use them if you just change the frame to include "unbounded following", but can't test this now. – James Z Sep 16 '14 at 15:51
  • 1
    @Leo: If the range ends at "current row", the last_value() must by definition be the current row. So that does explain why neither sort order nor partition matters with the default range. – Andomar Sep 16 '14 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.