FIRST_VALUE(), LAST_VALUE() and NTH_VALUE()

These three functions let you access specific rows within a window without needing a self-join or correlated subquery. They're perfect for benchmark comparisons — "show each row alongside the best value in its group" — and for extracting reference points from ordered sequences.

FIRST_VALUE()

Returns the value from the first row of the window frame. With PARTITION BY, "first" means first within each partition:

-- Each product alongside the top revenue product in its category
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    category,
    revenue,
    FIRST_VALUE(name) OVER (
        PARTITION BY category
        ORDER BY revenue DESC
    )                           AS top_product_in_category,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY category
        ORDER BY revenue DESC
    )                           AS top_revenue_in_category,
    ROUND(
        revenue * 100.0
        / FIRST_VALUE(revenue) OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ),
        1
    )                           AS pct_of_category_top
FROM product_revenue
ORDER BY category, revenue DESC;

Every product in the Hardware category sees the Hardware top-seller's name and revenue. This lets you compute gap-to-leader metrics without any join.

LAST_VALUE() — the frame trap

LAST_VALUE() is conceptually simple but has a critical gotcha. By default, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which means "last value" is actually the current row's own value, not the final row of the partition.

-- ❌ WRONG — returns each row's own revenue, not the partition minimum
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY category
        ORDER BY revenue DESC
    ) AS lowest_in_category   -- this is NOT lowest, it's just revenue again
FROM product_revenue;

To get the true last row of the partition, explicitly extend the frame:

-- ✅ CORRECT — extend frame to UNBOUNDED FOLLOWING
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    category,
    revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY category
        ORDER BY revenue DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )                           AS lowest_in_category
FROM product_revenue
ORDER BY category, revenue DESC;

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING expands the frame to include the entire partition. Now LAST_VALUE(revenue) with ORDER BY revenue DESC correctly returns the minimum (last when sorted descending).

This is the most common LAST_VALUE mistake. Always pair it with an explicit full-partition frame.