FIRST_VALUE()
SQL · Window Functions
Theory
FIRST_VALUE(column) returns the value of column from the first row of the window frame, using the window ORDER BY. With PARTITION BY dept ORDER BY salary DESC, the first row of each department is its highest earner, so FIRST_VALUE(name) puts the top earner's name on every row of that department.
The frame matters, but for FIRST_VALUE the default frame (from the start of the partition up to the current row) always contains the first row, so the default usually works. You can also pull a different column from the first row, which is the main advantage over MIN or MAX: MAX(salary) tells you the top salary, while FIRST_VALUE(name) ORDER BY salary DESC tells you who earns it. If there is a tie for first place, which tied row comes first is not defined unless you add a tie-breaker column.
Why we use it
Comparing every row with the 'best' or 'earliest' row of its group is common: gap to the top earner, a customer's first product, the opening price of the day. FIRST_VALUE brings that row's value onto every row without a self-join.
When to use it
Use FIRST_VALUE when you need a value (often a non-numeric one like a name) from the first row of an ordered group. If you only need the smallest or largest number, MIN or MAX OVER is simpler.
How it works
- Rows split by PARTITION BY and sorted by ORDER BY
- Build the frame for the current row
- Default frame starts at the first row of the partition
- Read the column value from the first row of the frame
- Same first value shown on each row of the group
Syntax
FIRST_VALUE(column) OVER (PARTITION BY col ORDER BY col)Example
SELECT name, department, salary,
FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;Key points
- Often paired with PARTITION BY to get the 'best' row per group
- Can return a different column than the one you sort by
- Default frame is fine since it always starts at the first row
- Combine with a frame clause to control exactly which rows count
- Add a tie-breaker so ties give a predictable result