FIRST_VALUE()

Syntax

How it works

FIRST_VALUE() is a window function. Instead of collapsing rows the way an aggregate does, it looks along an ordered window and returns the value found in the first row of that window, repeating it on every row of the group.

The typical shape is FIRST_VALUE(column) OVER (PARTITION BY group_col ORDER BY sort_col). The ORDER BY decides which row counts as first, and the optional PARTITION BY restarts the calculation for each group. This is the standard way to bring the top row of a group onto every row: the best selling product per category, the first order date per customer, or the highest score in each class.

Once the first value sits on every row you can compare each row against it, for example measuring how far each order is from a customer first order, or expressing a value as a share of the group leader. FIRST_VALUE is available in MySQL 8.0 and later, SQLite 3.25 and later, and in all supported versions of PostgreSQL and SQL Server. Its mirror image is LAST_VALUE(), and NTH_VALUE() reaches any position in between.

Examples

First order date per customer

SELECT customer_id, order_id, order_date,
       FIRST_VALUE(order_date)
         OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order
FROM orders
ORDER BY customer_id, order_date;

customer_id | order_id | order_date | first_order
------------+----------+------------+------------
          1 |      101 | 2026-01-03 | 2026-01-03
          1 |      140 | 2026-02-11 | 2026-01-03
          2 |      118 | 2026-01-20 | 2026-01-20

Best score in each group on every row

-- highest score per class, shown against each student
SELECT class, student, score,
       FIRST_VALUE(score)
         OVER (PARTITION BY class ORDER BY score DESC) AS top_score
FROM exam_results
ORDER BY class, score DESC;

class | student | score | top_score
------+---------+-------+----------
 A     | Sara    |    95 |       95
 A     | Omar    |    88 |       95
 B     | Lina    |    91 |       91

Compare each row to the group first value

-- days since the customer first order
SELECT customer_id, order_date,
       order_date - FIRST_VALUE(order_date)
         OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_first
FROM orders
ORDER BY customer_id, order_date;

customer_id | order_date | days_since_first
------------+------------+-----------------
          1 | 2026-01-03 |               0
          1 | 2026-02-11 |              39
          2 | 2026-01-20 |               0

Share of the best selling product

-- each product sales as a share of the category leader
SELECT category, product, units,
       ROUND(100.0 * units / FIRST_VALUE(units)
         OVER (PARTITION BY category ORDER BY units DESC), 1) AS pct_of_top
FROM product_sales
ORDER BY category, units DESC;

category | product | units | pct_of_top
---------+---------+-------+-----------
 Phones   | P-Max   |   900 |      100.0
 Phones   | P-Mini  |   540 |       60.0
 Tablets  | T-10    |   300 |      100.0

First status change per ticket

SELECT ticket_id, changed_at, status,
       FIRST_VALUE(status)
         OVER (PARTITION BY ticket_id ORDER BY changed_at) AS opening_status
FROM ticket_history
ORDER BY ticket_id, changed_at;

ticket_id | changed_at | status  | opening_status
----------+------------+---------+---------------
       10 | 2026-03-01 | open    | open
       10 | 2026-03-02 | pending | open
       11 | 2026-03-01 | open    | open

Common mistakes

-- No ORDER BY: which row is first is undefined,
-- so first_order can differ between runs
SELECT customer_id, order_date,
       FIRST_VALUE(order_date)
         OVER (PARTITION BY customer_id) AS first_order
FROM orders;

SELECT customer_id, order_date,
       FIRST_VALUE(order_date)
         OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order
FROM orders;

-- Expecting the last value here, but this returns
-- the FIRST value: the default frame runs from the
-- start of the partition to the current row
SELECT category, product, units,
       LAST_VALUE(units)
         OVER (PARTITION BY category ORDER BY units DESC) AS top_units
FROM product_sales;

-- FIRST_VALUE needs no special frame, because the
-- default frame already starts at the first row
SELECT category, product, units,
       FIRST_VALUE(units)
         OVER (PARTITION BY category ORDER BY units DESC) AS top_units
FROM product_sales;

Performance

FIRST_VALUE() is cheap once the rows are ordered: it reads a single value from the first row of the frame. The real cost is the sort that the ORDER BY and PARTITION BY imply. An index that already matches the partition and order columns lets the engine skip that sort.

Because the default frame stretches from the first row of the partition to the current row, FIRST_VALUE does not force the whole frame to be materialised the way an unbounded-following frame does. If you need the last row instead, be aware that LAST_VALUE() with a full frame scans further and can be more expensive.

When many window functions share the same PARTITION BY and ORDER BY, most engines compute the ordering once and reuse it, so grouping related window calls together helps the optimiser.

Interview questions

SELECT category, product,
       FIRST_VALUE(product)
         OVER (PARTITION BY category ORDER BY units DESC) AS best_seller
FROM product_sales;