SQL Server – Understanding the last_value() Window Function

sql serverwindow functions

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?

Best Answer

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.

Related Solutions

Postgresql – Working of window functions and idea window size for window function

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.

The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.

While certain tasks can be tackled with either of these tools, they are very different in nature.

For your simple task

sorting data on date and then bring the latest data first

you don't need either of them. Just add:

ORDER BY date_col DESC

According to your comment, you would need:

SELECT col1, col2
FROM   tbl
ORDER  BY date_col DESC
LIMIT  100   -- 100 latest rows
OFFSET 0;    -- just noise, but may be easier to code

Retrieve more:

...
LIMIT  100
OFFSET 100;  -- next page of 100 rows ...

Be sure to have an index on date_col in either case!

Postgres – Window Function Rank and Count

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank().

When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical.

If you modify your query have multiple windows as

SELECT compUserId, rank, totalUsers 
FROM (
    SELECT cu.competition_user_id as compUserId, cu.user_id as userId,  
    count(*) OVER (PARTITION BY cu.competition_id) as totalUsers, 
    rank() OVER (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) as rank 
    FROM competition_users cu 
    LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
    LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
    WHERE cu.left_competition = false 
    AND cu.competition_id in (:compIds)
) as sub 
WHERE compUserId in (:compUserIds);

so that you are only applying the PARTITION BY to your COUNT(*) window, and have both PARTITION BY and ORDER BY clauses for your rank(), I believe you'll get the results you want.

Refer to this SQL FIDDLE as a reference, where I have a generic id field, a com_num field to represent the competition id, and a com_time field to represent a competitors time.

Best Answer

Related Solutions

Postgresql – Working of window functions and idea window size for window function

Postgres – Window Function Rank and Count

Related Question