FIRST_VALUE, LAST_VALUE, NTH_VALUE in SQL
FIRST_VALUE, LAST_VALUE, and NTH_VALUE return the value of an expression from a specific position within the window: the first row, the last row, or the nth row. Their result for each input row is a positional lookup: the value of the expression at a designated position within the ordered partition, retrieved and attached to every row in the window.
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.
Every 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.
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.
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.
9 FIRST_VALUE, LAST_VALUE, NTH_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.
These problems are part of the FIRST_VALUE, LAST_VALUE, NTH_VALUE lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.