LAST_VALUE

Hook: Interviewers love LAST_VALUE because it looks obvious, but the real test is whether you know the window frame can stop at the current row.

Question: What does LAST_VALUE do in SQL?

Answer: LAST_VALUE returns the value from the last row in the current window frame, not automatically the last row in the whole partition. With the default frame used alongside ORDER BY, many databases stop the frame at the current row, so the result can look like the row you are already on.

Interview-Ready Answer: I use LAST_VALUE when I want the final value in an ordered window, but I always remember it works on the frame, not the whole partition by default. The big gotcha is that the default frame usually ends at the current row, so to get the true last value for the full group I add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. I also make the ORDER BY deterministic with a tie-breaker if timestamps can repeat.

What it really means

Detailed Explanation: Think of a window function as a moving camera over a sorted partition of rows. LAST_VALUE(x) looks at the rows currently visible to that camera and returns the x value from the last visible row. That is why the frame matters so much: the function does not magically know you want the last row in the entire partition unless you tell it so.

1. Partition the data. Rows are split by PARTITION BY. Each group is handled separately.
2. Sort each partition. ORDER BY defines the logical order inside the group. Without a stable order, “last” is not meaningful.
3. Build the frame for the current row. In standard SQL, when you use ORDER BY but no explicit frame, the default is typically RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That means the frame ends at the current row, not the end of the partition.
4. Pick the last row inside that frame. LAST_VALUE then returns the expression from that row.
5. Widen the frame when you want the true final row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so every row can see the whole partition.

Why this trips people up

Many candidates mentally read “last value” as “last row in the group.” But SQL window functions are framed. A frame is the subset of rows visible to the function for the current row. If that frame ends at the current row, then the last visible row is often the current row itself, which makes LAST_VALUE feel broken when it is actually doing exactly what you asked.

When to use it

• Latest status by time: show the final order state, latest event, or closing balance.
• Per-row comparison: compare each row to the final value in its group.
• Reporting snapshots: stamp every row with the group’s final result.

When not to use it

• If you only need a group aggregate, a simple aggregate or subquery can be clearer.
• If you really want the maximum numeric value, use MAX; that is a different question.
• If the order column has ties and you do not add a tie-breaker, the result can be unstable across runs or engines.

Performance and engine behavior

The expensive part is usually the sort. If a partition has 1,000 rows, this is trivial; if it has 1,000,000 rows, the engine may sort the partition first and then scan it. Roughly speaking, the work is O(n log n) for sorting plus O(n) for the window pass. In real systems, once the partition exceeds the memory grant or work area, the sort can spill to disk, and latency can jump from milliseconds to seconds. That is why indexes that match the partition and order keys can help a lot.

Important edge cases

• No deterministic order: if two rows tie on the ORDER BY columns, add a unique column such as an id or timestamp+id pair.
• Default frame surprise: if you do not specify a frame, you may only be looking at rows up to the current row.
• NULL handling: different databases vary in support for IGNORE NULLS; do not assume it exists everywhere.
• Peer groups: with RANGE, rows with equal sort keys can act like one peer group, which changes what “last” means.

Real-World Example: Imagine a checkout service that tracks order events: created, paid, packed, shipped, delivered. A product dashboard wants to show the final status next to every event so support can see where an order ended up. The engineer writes LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY event_time) and thinks it means final status.

In production, the dashboard starts showing the wrong answer: every row seems to carry its own status instead of the final status of the order. No SQL error appears, so the bug hides in plain sight. Support agents think orders are stuck at packed or shipped even after they were delivered, and the team wastes time chasing phantom delivery issues. The fix is simple but critical: add an explicit frame that reaches unbounded following, and add a tie-breaker if multiple events share the same timestamp.

The lesson is that a small misunderstanding in window framing can create a business bug, not just a query bug. The SQL runs, the numbers look plausible, and the bad result is exactly why LAST_VALUE is a favorite interview question.

Follow-up & Tricky Questions:

• What is the default frame for LAST_VALUE? With ORDER BY, the standard default is commonly RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is why the function often returns the current row unless you extend the frame.
• How do you get the last value of the whole partition? Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use a subquery that orders descending and picks the first row.
• How is LAST_VALUE different from MAX? LAST_VALUE follows row order; MAX follows value comparison. If the newest row is not the largest value, they return different answers.
• What if two rows have the same timestamp? Add a unique tie-breaker like event_id so the order is deterministic. Otherwise the identity of the “last” row can vary.
• When would you use FIRST_VALUE instead? Use it when the important thing is the starting point of the group, such as the first price, first status, or original signup value.
• Can I use LAST_VALUE without ORDER BY? Technically some engines allow it, but “last” becomes meaningless because there is no defined order. In interviews, the safe answer is that you should always pair it with ORDER BY.
• Does LAST_VALUE ignore NULLs? Not by default in every database. Some engines support IGNORE NULLS, but behavior is vendor-specific, so you should check the target SQL dialect.
• Why does it sometimes match the current row? Because the default frame ends at the current row, so the last visible row is the row you are already on. That is the classic gotcha.

Common Mistakes:

• Thinking it means last row in the partition. Correct it by remembering it means last row in the frame.
• Forgetting the explicit frame. Correct it by adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you want the whole group.
• Using a non-unique ORDER BY. Correct it by adding a tie-breaker column so the last row is deterministic.
• Confusing it with MAX. Correct it by asking whether you want the newest row or the largest value.

Memory Hook: LAST_VALUE is the end of your flashlight beam, not the end of the hallway. If you do not widen the beam with an explicit frame, you only see up to the current row.

Cheat Sheet:

• LAST_VALUE returns the last row in the current frame.
• Default frame with ORDER BY often ends at CURRENT ROW.
• Use UNBOUNDED FOLLOWING for the true last row of the partition.
• Add a unique tie-breaker to make ordering deterministic.
• LAST_VALUE is order-based; MAX is value-based.

Practice Tasks:

• Write a query that shows the first and last order status side by side for each event.
• Modify the sample query so it returns the final status only for the last row of each order.
• Add a nullable status row and experiment with how your database handles NULLs in the window.