FIRST_VALUE and LAST_VALUE in PostgreSQL: First and Last per Partition and the Frame Trap

FIRST_VALUE and LAST_VALUE are window functions that return the value from the first or last row of a window, without collapsing the result into a single row. They answer questions like "what was this user's first order" or "who earns the most in this department" while keeping every original row in place.

Syntax and a basic example

Both functions run on top of OVER (...) with a mandatory ORDER BY inside the window; without it, "first" and "last" have no meaning. PARTITION BY defines the groups the window is computed within.

SELECT
    id,
    dept,
    name,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY dept
        ORDER BY salary DESC
    ) AS top_earner
FROM employees;

Here, for every employee, the top_earner column holds the name of the highest-paid person in their department. A few behaviors worth knowing:

• The result is a scalar from a specific row of the window, not an aggregate. Its type matches the argument.
• FIRST_VALUE "just works" with the default frame, because the window's start is always pinned to the first row.
• LAST_VALUE with the same default frame is treacherous, as shown below.

The LAST_VALUE frame trap

The most common mistake: write LAST_VALUE and get the current row instead of the last one. The cause is the default window frame, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The window grows from the start of the partition up to the current row, so its "last" row is always the current one.

-- WRONG: returns the current row's score, not the partition's last
SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
    ) AS wrong_last
FROM scores;

To let LAST_VALUE see the whole partition, widen the frame explicitly to both edges of the window:

SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

Gotcha: FIRST_VALUE is immune to this trap because the frame's left edge is always UNBOUNDED PRECEDING. But the moment you add an ORDER BY to the window, the default frame clips the right edge to CURRENT ROW, and that is exactly what breaks LAST_VALUE. When in doubt, spell out the frame.

First and last in one query

You often need both edges at once, for example a user's first and last order by time. To avoid repeating a long window definition, factor it out into a WINDOW clause:

SELECT DISTINCT
    user_id,
    FIRST_VALUE(amount) OVER w AS first_order_amount,
    LAST_VALUE(amount)  OVER w AS last_order_amount
FROM orders
WINDOW w AS (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

DISTINCT collapses the identical partition rows into one. Note that both calls share the same frame w, so FIRST_VALUE takes the earliest order and LAST_VALUE the latest.

When something simpler fits

FIRST_VALUE and LAST_VALUE shine when you need to keep every row and tack an edge value onto each. But if you only want one row per group, other constructs are often terser:

• In PostgreSQL, DISTINCT ON (user_id) ... ORDER BY user_id, created_at returns the first row of a group without any window.
• For a per-group aggregate, a plain MAX(salary) / MIN(salary) with GROUP BY dept is enough.
• NTH_VALUE(amount, 2) OVER w grabs the Nth row of the window and also needs a wide frame.

Differences in MySQL and ClickHouse

MySQL 8+ has FIRST_VALUE and LAST_VALUE and they behave identically, including the same default-frame trap; widen the window with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING the exact same way. MySQL 5.7 has no window functions at all, so you emulate them with subqueries or GROUP_CONCAT.

-- MySQL 8: same frame trap, same fix
SELECT
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

ClickHouse supports these too, but argMax(name, salary) and argMin(name, salary) are often handier: they return one column's value at the row with the max or min of another, with no explicit frame. When you specifically need a per-row result that preserves every row, the window form with a wide frame stays the most portable.