FIRST_VALUE and LAST_VALUE in PostgreSQL - Step-by-Step Execution

Concept Flow - FIRST_VALUE and LAST_VALUE

Start with ordered data

↓

Apply window function

↓

FIRST_VALUE: pick first row value in window

↓

LAST_VALUE: pick last row value in window

↓

Return result for each row

These functions look at a set of rows ordered in a window and return the first or last value from that set for each row.

Execution Sample

PostgreSQL

SELECT id, value,
  FIRST_VALUE(value) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_val,
  LAST_VALUE(value) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM sample_table
ORDER BY id;

This query shows how FIRST_VALUE and LAST_VALUE return the first and last 'value' in the ordered rows by 'id'.

Execution Table

Step	Row id	value	Window Frame	FIRST_VALUE(value)	LAST_VALUE(value)
1	1	10	[1..1]	10	10
2	2	20	[1..2]	10	20
3	3	30	[1..3]	10	30
4	4	40	[1..4]	10	40
5	5	50	[1..5]	10	50

💡 All rows processed; window frame moves with each row; FIRST_VALUE always first row's value; LAST_VALUE depends on frame definition.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5
FIRST_VALUE(value)	N/A	10	10	10	10	10
LAST_VALUE(value)	N/A	10	20	30	40	50

Key Moments - 2 Insights

Why does LAST_VALUE sometimes not return the last value you expect?

Because LAST_VALUE depends on the window frame. Without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the frame ends at the current row, so LAST_VALUE returns the current row's value, not the last in the entire partition.

Does FIRST_VALUE change as we move through rows?

No, FIRST_VALUE always returns the first value in the window frame, which usually starts at the first row, so it stays the same for all rows.

Visual Quiz - 3 Questions

Look at the execution table, what is FIRST_VALUE(value) at row id 3?

A 20

B 30

C 10

D 40

💡 Hint

Check the FIRST_VALUE(value) column for row id 3 in the execution_table.

At which row does LAST_VALUE(value) change from 30 to 40?

A Row 2

B Row 4

C Row 3

D Row 5

💡 Hint

Look at the LAST_VALUE(value) column in the execution_table and find where it changes.

If the window frame for LAST_VALUE was not set to UNBOUNDED FOLLOWING, what would LAST_VALUE return at row 5?

A 50

B 40

C 10

D N/A

💡 Hint

Consider that without UNBOUNDED FOLLOWING, the frame ends at the current row, so LAST_VALUE returns the current row's value.

Concept Snapshot

FIRST_VALUE(value) returns the first value in the ordered window.
LAST_VALUE(value) returns the last value in the window frame.
Window frame defaults affect LAST_VALUE results.
Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for full partition.
Both functions return a value for each row in the result.

Full Transcript

This lesson shows how FIRST_VALUE and LAST_VALUE work in PostgreSQL. We start with data ordered by id. FIRST_VALUE always picks the first value in the window, so it stays the same for all rows. LAST_VALUE picks the last value in the window frame, which depends on how the frame is defined. If the frame ends at the current row, LAST_VALUE returns the current row's value. To get the last value of the entire partition, we set the frame to include all rows from the start to the end. The execution table shows these values step by step for each row. Understanding window frames is key to using LAST_VALUE correctly.