SQL / WINDOW FUNCTIONS
First, last, and nth values within a partition
Pull a specific row's value into every row of a partition with FIRST_VALUE, LAST_VALUE and NTH_VALUE, and set the frame so you get the row you meant.
What you will learn
- FIRST_VALUE is safe with the default frame; LAST_VALUE needs UNBOUNDED FOLLOWING
- NTH_VALUE(col, n) is 1-based and yields NULL when the frame holds fewer than n rows
- Add a tiebreaker to ORDER BY so the first and last row of a partition are unique
- Define the frame once in a WINDOW clause and reuse it across several value columns
Understanding First, last, and nth values within a partition
FIRST_VALUE, LAST_VALUE and NTH_VALUE are not aggregates: each picks one row out of the ordered set of rows visible to the current row and hands back one of its column values, copied onto every row of the partition. The mental model is a numbered list — ORDER BY inside OVER builds the list, and the three functions read position 1, the final position, and position n of it. Because nothing is being summed, the argument can be text, a date or a boolean: MAX(points) tells you the highest score, while FIRST_VALUE(player) over an ORDER BY points DESC tells you who scored it.
That list is the frame, not the partition, and that is where the surprises come from. An OVER clause with ORDER BY and no frame clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, a list that is complete at the front and grows one row at a time. Position 1 of such a list never changes, which is why FIRST_VALUE behaves as people expect, but the final position is the current row, so LAST_VALUE just echoes its own argument and NTH_VALUE(x, 3) stays NULL until the third row arrives. Widening the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes the list the whole partition, and all three then read the row you intended.
Once you think of these functions as reading a position, two things follow. The ORDER BY has to make that position unique: with ties in the ordering column the executor is free to put either row first, so write ORDER BY points DESC, player rather than trusting the plan. And a narrow frame is a feature, not only a trap — FIRST_VALUE over ROWS BETWEEN 2 PRECEDING AND CURRENT ROW returns the oldest row of a sliding three-row window and clamps to the partition start instead of going NULL there.
WITH sales(region, sold_on, amount) AS (
VALUES ('East', DATE '2024-01-05', 100),
('East', DATE '2024-02-11', 250),
('East', DATE '2024-03-02', 175),
('West', DATE '2024-01-20', 300),
('West', DATE '2024-02-15', 90),
('West', DATE '2024-03-30', 410)
)
SELECT region,
sold_on,
amount,
FIRST_VALUE(amount) OVER w AS first_amt,
LAST_VALUE(amount) OVER w AS last_amt_default,
LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sold_on
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_amt_full
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sold_on)
ORDER BY region, sold_on;FIRST_VALUE, LAST_VALUE and NTH_VALUE return a column value from a position in the current frame, so the frame — not the partition — decides which row you read.
Worked examples
Top scorer and runner-up per team
Returns an attribute of the extreme row rather than the extreme value, using NTH_VALUE for second place.
WITH scores(team, player, points) AS (
VALUES ('Red', 'Ana', 31),
('Red', 'Bo', 44),
('Red', 'Cy', 22),
('Blue', 'Dee', 18),
('Blue', 'Eli', 18),
('Green','Fay', 50)
)
SELECT team, player, points,
FIRST_VALUE(player) OVER w AS top_scorer,
NTH_VALUE(player, 2) OVER w AS runner_up
FROM scores
WINDOW w AS (PARTITION BY team ORDER BY points DESC, player
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY team, points DESC, player;Example explained
Line 1The frame runs to UNBOUNDED FOLLOWING, so every row of a team sees all of that team's rows and the two new columns are constant per team.
Line 2FIRST_VALUE(player) returns a name, not a number; MAX(points) could never tell you which player earned it.
Line 3NTH_VALUE(player, 2) counts from the first row of the frame, so with points DESC it is the second-highest scorer.
Line 4Green has a single row, so the frame never contains a second row and NTH_VALUE returns NULL, which psql prints as a blank cell.
Percent change from the partition's opening value
Shows why FIRST_VALUE needs no frame clause, and reuses it inside arithmetic to compare each row to its anchor.
WITH ticks(sym, ts, px) AS (
VALUES ('AAA', 1, 100.0),
('AAA', 2, 110.0),
('AAA', 3, 95.0),
('BBB', 1, 40.0),
('BBB', 2, 50.0)
)
SELECT sym, ts, px,
FIRST_VALUE(px) OVER (PARTITION BY sym ORDER BY ts) AS open_px,
ROUND(100.0 * (px - FIRST_VALUE(px) OVER (PARTITION BY sym ORDER BY ts))
/ FIRST_VALUE(px) OVER (PARTITION BY sym ORDER BY ts), 1) AS pct_vs_open
FROM ticks
ORDER BY sym, ts;Example explained
Line 1No frame clause is needed here: the partition's first row is already inside RANGE UNBOUNDED PRECEDING AND CURRENT ROW from the very first row onward.
Line 2The identical window expression appears three times; the engine evaluates one window and reads the same result for each mention.
Line 3Row (AAA, 1) is compared with itself and gives 0.0, a quick check that the anchor row is the one you expect.
Line 4ROUND(..., 1) is applied because numeric division in PostgreSQL widens the scale, which would otherwise print 25.0000000000000000.
FIRST_VALUE reads a frame, LAG reads a row offset
Contrasts a bounded frame's first row with a fixed offset at the partition boundary.
WITH readings(t, val) AS (
VALUES (1, 5), (2, 9), (3, 4), (4, 7), (5, 6)
)
SELECT t, val,
FIRST_VALUE(val) OVER (ORDER BY t
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS val_3_back,
LAG(val, 2) OVER (ORDER BY t) AS lag2
FROM readings
ORDER BY t;Example explained
Line 1ROWS BETWEEN 2 PRECEDING AND CURRENT ROW holds at most three rows, and FIRST_VALUE returns the oldest one in it.
Line 2At t = 1 and t = 2 the frame is clipped at the partition start, so FIRST_VALUE falls back to the earliest available row while LAG(val, 2) has nothing to point at and returns NULL.
Line 3From t = 3 on the frame is full, its first row is exactly two rows back, and the two columns agree.
Important notes
In the default RANGE frame, CURRENT ROW means the end of the current row's peer group, so with duplicate ORDER BY keys LAST_VALUE returns the last tied row rather than the row you are on; switching the frame to ROWS makes it the row itself.
Dialects differ: PostgreSQL and SQLite have no FROM LAST or IGNORE NULLS for these functions, and SQL Server has no NTH_VALUE at all — there you reverse the ORDER BY and use FIRST_VALUE, or fall back to ROW_NUMBER.
Common mistakes
Writing LAST_VALUE(x) OVER (PARTITION BY p ORDER BY o) and stopping there: the default frame ends at the current row, so the column is a copy of x and every 'latest value' figure in the report is wrong except on the last row of each partition.
Treating NTH_VALUE as 0-based: NTH_VALUE(x, 0) raises an error, and since NTH_VALUE(x, 1) is exactly FIRST_VALUE(x), an off-by-one shifts every answer one row.
Ordering by a column that has duplicates with no tiebreaker, so FIRST_VALUE returns whichever tied row the executor happened to place first and the result changes when the data or the plan changes.
Try it yourself
Change, predict, then run
In a browser editor build a six-row table of (city, month, rainfall) covering two cities, then add columns for each city's first, last, and second-wettest rainfall. Delete the ROWS BETWEEN clause and state which of the three columns changed and why.
Open the SQL workspaceCheck your understanding
A query computes LAST_VALUE(price) OVER (PARTITION BY sym ORDER BY ts) and every row's result turns out to equal that row's own price. What explains it?
- ORDER BY ts must be written DESC before LAST_VALUE can reach later rows
- ts contains duplicate values, so each frame collapsed to a single row
- The default frame ends at the current row, so the frame's last row is the current row
- LAST_VALUE ignores PARTITION BY unless the window is named in a WINDOW clause
Show answer
With ORDER BY and no frame clause the window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the final position in the frame is always the row being computed; ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING fixes it. Flipping to DESC is tempting because it does bring the latest tick to the front, but it changes only the ordering and not where the frame ends — LAST_VALUE would still echo the current row, and you would reach for FIRST_VALUE instead.