FIRST_VALUE, LAST_VALUE, NTH_VALUE in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What are FIRST_VALUE and LAST_VALUE in SQL?
FIRST_VALUE, LAST_VALUE, and NTH_VALUE pick up the actual column value sitting at a specific position in an ordered partition and attach it to every row in the group.
You already know window functions can compute running totals or assign ranks. These three do something different: they broadcast a value from one specific position across the entire partition. Every row gets that same value alongside its own data. The most common use is attaching context from one row to an entire group — like tagging every order with the amount of the customer's first purchase.
SELECT customer_id, ordered_at::date, total_amount,
FIRST_VALUE(total_amount) OVER (
PARTITION BY customer_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_order_amount
FROM orders
ORDER BY customer_id, ordered_at
LIMIT 10Every row for each customer receives the total_amount of their first order. The ORDER BY inside OVER decides which row is "first" — here, the earliest ordered_at. FIRST_VALUE picks up that row's value and puts it on every other row in the partition — no join needed.
Why does LAST_VALUE return the current row?
LAST_VALUE surprises almost everyone
LAST_VALUE seems like it should return the value from the final row of the partition. By default, it doesn't.
The default behavior is to look only as far as the current row. So for each row, "last" means the last row seen so far — which is the current row itself. LAST_VALUE ends up returning the current row's own value for most of the partition, which is rarely what you want.
To get the actual last row of the partition, extend the frame explicitly:
LAST_VALUE(event_type) OVER (
PARTITION BY session_id
ORDER BY event_time
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)The extra line tells the function to look all the way to the end of the partition, not just to the current row. With that in place, every row in the session receives the final event type.
How do you get the nth row of a partition in SQL?
NTH_VALUE
NTH_VALUE(revenue, 3) returns the value at a specific position in the partition — in this case, the third row by the ORDER BY sequence. Position counting starts at 1. If the partition has fewer rows than the requested position, it returns NULL.
NTH_VALUE has the same quirk as LAST_VALUE: if the target position is ahead of the current row, the function returns NULL unless you tell it to look forward. The safe pattern when targeting any fixed position is to cover the full partition:
NTH_VALUE(revenue, 3) OVER (
PARTITION BY region
ORDER BY revenue DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)This returns the third-highest revenue for each region, attached to every row in that region. The frame clause is the mechanism — N044 covers it in depth. For now, use this pattern whenever LAST_VALUE or NTH_VALUE looks like it's returning the wrong row.
Practice FIRST_VALUE and LAST_VALUE in SQL
Brightlane's CRM team is building a customer order history view. Every order should be annotated with that customer's initial purchase amount for easy reference.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's very first order chronologically.
Assumptions:
- The
orderstable has one row per order with anid, acustomer_id, atotal_amount, and anordered_attimestamp. - A customer's first order is the order with the smallest
ordered_atfor thatcustomer_id. The same first-order amount appears on every row sharing acustomer_id. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andfirst_order_amount. Sorted bycustomer_id, thenordered_at.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 FIRST_VALUE and LAST_VALUE practice problems
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's very first order chronologically.
Write a query to return every order's ID, customer ID, status, and the status of that same customer's first order chronologically.
Write a query to return every session's ID, user ID, event count, and the event count from that same user's first session chronologically.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's final order chronologically.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's second order chronologically.
Write a query to return every delivered order's ID, customer ID, amount, and the amount of that customer's first delivered order chronologically.
Write a query to return every order's ID, customer ID, order amount, and the difference between that order's amount and the same customer's first order amount chronologically.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's third order chronologically.
Write a query to return every order's ID, customer ID, order amount, the amount of that same customer's first order chronologically, and the amount of that same customer's most recent order chronologically.
Start learning to practice all 9 FIRST_VALUE and LAST_VALUE problems, with instant grading and mastery tracking.
Common questions about FIRST_VALUE and LAST_VALUE
What does NTH_VALUE return when the position is past the end?
NULL. Asking for the ninth row of a two-row partition is not an error, so a report can quietly fill with NULLs when the groups are smaller than you assumed. Check the group sizes before trusting a fixed position.
Does FIRST_VALUE need a frame clause?
No. The default frame already starts at the beginning of the partition, so the first row is in view from the outset. LAST_VALUE is the one that needs a frame, because the default stops at the current row rather than the end.
Do these functions need an ORDER BY inside OVER?
They run without one, which is the trap. With no ordering the partition has no first or last in any meaningful sense, so you get a value from an arbitrary row rather than an error telling you something is missing.
