SQL FIRST_VALUE and LAST_VALUE Explained

A manager wants to see each employee's salary alongside the salary of the first person hired in their department. That reference point should appear on every row, not just the top one. FIRST_VALUE puts it there without a join or subquery.

How FIRST_VALUE Works

FIRST_VALUE is a window function that looks at the rows in your window frame, takes the value from the first row in that frame, and attaches it to every row. The frame is defined by ORDER BY inside OVER. The first row is whichever row sorts to the top.

Basic syntax:

SELECT
    column_name,
    FIRST_VALUE(column_name) OVER (
        PARTITION BY group_column
        ORDER BY sort_column
    ) AS first_val
FROM table_name;

Here it is on the employees table. This returns each employee's salary alongside the salary of the first person hired in their department:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
    ) AS first_hire_salary
FROM employees e;

Every row in the Engineering department, for example, shows the same first_hire_salary: the salary of whoever was hired first in that department. The ORDER BY e.hire_date inside OVER defines what "first" means. Change it to ORDER BY e.salary and you'd get the lowest salary instead.

How LAST_VALUE Works, and Why It Needs a Frame Clause

LAST_VALUE works like FIRST_VALUE in reverse. It returns the value from the last row in the window frame. The problem is that the default frame ends at the current row, not at the last row in the partition.

Without a frame clause, LAST_VALUE returns the current row's own value on most rows, because the frame only goes up to where you are. That's almost never what you want.

To fix this, you need to extend the frame explicitly:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    LAST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_hire_salary
FROM employees e;

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING tells SQL to include every row in the partition, from first to last. Now last_hire_salary correctly shows the salary of the most recently hired person in each department.

This frame clause is required for LAST_VALUE to work as expected. You'll rarely need it for FIRST_VALUE because the default frame already starts at the beginning.

Using Both Together

You can use FIRST_VALUE and LAST_VALUE in the same query to compare a row against both ends of its group. This query shows each employee's salary alongside both the earliest and latest hire's salary in their department:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
    ) AS first_hire_salary,
    LAST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_hire_salary
FROM employees e;

Both columns appear on every row. No aggregation, no joins, no subqueries.

Without PARTITION BY

Drop PARTITION BY and both functions operate across the entire result set. Every row gets the same value: the first or last in the full sort order.

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    FIRST_VALUE(o.total_amount) OVER (ORDER BY o.order_date) AS first_order_amount,
    LAST_VALUE(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders o;

first_order_amount is the total_amount of the earliest order. last_order_amount is the total_amount of the most recent order. Both appear on every row.

How This Connects to Other Concepts

FIRST_VALUE and LAST_VALUE use the same OVER clause as [LAG and LEAD](/learn/sql-lag-lead-in-window-function). LAG and LEAD access adjacent rows by offset. FIRST_VALUE and LAST_VALUE access the boundary rows of the entire frame. If you need the minimum or maximum value regardless of sort order, [MIN and MAX as window functions](/learn/sql-min-max-in-window-function) are a simpler choice. Use FIRST_VALUE or LAST_VALUE when the sort order matters and you want the value at the boundary, not the numeric extreme.

Common Mistakes

1. Using LAST_VALUE without a frame clause

Without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the default frame ends at the current row, so LAST_VALUE returns the current row's own value on most rows.

-- wrong: returns current row's value, not the partition's last row
SELECT
    e.employee_id,
    e.department,
    e.salary,
    LAST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
    ) AS last_hire_salary
FROM employees e;

Add the explicit frame clause to extend the window to the end of the partition.

-- correct
SELECT
    e.employee_id,
    e.department,
    e.salary,
    LAST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_hire_salary
FROM employees e;

2. Confusing FIRST_VALUE with MIN

FIRST_VALUE returns the value from the first row in the sort order. If you sort ascending by salary, FIRST_VALUE(salary) gives the lowest salary. But sort descending and it gives the highest. MIN always gives the lowest regardless of sort order.

-- wrong if the goal is the minimum salary: sort order controls FIRST_VALUE
SELECT
    e.department,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC -- descending: first row is the highest salary
    ) AS first_salary
FROM employees e;

Use MIN for the true minimum, or ensure your sort order matches your intent.

-- correct: ascending sort gives the lowest salary first
SELECT
    e.department,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.salary ASC
    ) AS lowest_salary
FROM employees e;

3. Filtering on a window alias in WHERE

Window functions run after WHERE, so you can't reference the alias at the same query level.

-- wrong
SELECT
    o.order_id,
    o.total_amount,
    FIRST_VALUE(o.total_amount) OVER (ORDER BY o.order_date) AS first_amount
FROM orders o
WHERE first_amount > 100;

Wrap the query in a subquery so the computed column exists before the filter runs.

-- correct
SELECT * FROM (
    SELECT
        o.order_id,
        o.total_amount,
        FIRST_VALUE(o.total_amount) OVER (ORDER BY o.order_date) AS first_amount
    FROM orders o
) t
WHERE first_amount > 100;

Quick Recap

• FIRST_VALUE(column) returns the value from the first row in the window frame, as defined by ORDER BY inside OVER.
• LAST_VALUE(column) requires ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to return the actual last row in the partition, not the current row.
• PARTITION BY makes both functions calculate independently within each group.
• FIRST_VALUE is not the same as MIN. The sort order inside OVER determines which row is "first," and that row's value may not be the smallest.
• Without PARTITION BY, both functions operate across the entire result set.
• To filter on the result of either function, wrap the query in a subquery or [CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    FIRST_VALUE(e.salary) OVER (ORDER BY e.hire_date) AS first_hire_salary
FROM employees e;

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
    ) AS dept_first_salary
FROM employees e;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    LAST_VALUE(pay.amount) OVER (
        PARTITION BY pay.method
        ORDER BY pay.payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_method_amount
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    FIRST_VALUE(pay.amount) OVER (
        PARTITION BY pay.method
        ORDER BY pay.payment_date
    ) AS first_method_amount,
    LAST_VALUE(pay.amount) OVER (
        PARTITION BY pay.method
        ORDER BY pay.payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_method_amount
FROM payments pay
WHERE pay.status = 'completed';

SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.payment_date,
        pay.amount,
        FIRST_VALUE(pay.amount) OVER (
            ORDER BY pay.payment_date
        ) AS first_payment_amount
    FROM payments pay
) t
WHERE amount > first_payment_amount;