WindowSQL standard

FIRST_VALUE

Takes the value from the first row of the window.

Syntax

FIRST_VALUE(<expression>) OVER (ORDER BY <sort key>)
FIRST_VALUE(<expression>) OVER (PARTITION BY <group> ORDER BY <sort key>)

Adds to every row the value from the first row of its window: the highest salary in the company, the name of the department's leader. The number of rows stays the same, so it's easy to compare side by side: how far each person is from the first. Its mirror function is LAST_VALUE, but that one needs its window frame set up.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The highest salary next to each one

SELECT name, salary,

FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS top_salary

FROM employees;

name	salary	top_salary
Olivia	1350	1350
Emma	1200	1350
Sophia	1200	1350
Liam	1000	1350
Noah	950	1350

The leader of each department

SELECT name, department, salary,

FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS leader

FROM employees;

name	department	salary	leader
Emma	backend	1200	Emma
Liam	backend	1000	Emma
Sophia	design	1200	Sophia
Olivia	frontend	1350	Olivia
Noah	frontend	950	Olivia

How far each salary is from the highest

SELECT name, salary,

FIRST_VALUE(salary) OVER (ORDER BY salary DESC) - salary AS gap_to_top

FROM employees;

name	salary	gap_to_top
Olivia	1350	0
Emma	1200	150
Sophia	1200	150
Liam	1000	350
Noah	950	400

Watch out

With equal values, any of the tied rows can become “first”. For a stable result, add a unique field to ORDER BY.

Unlike with LAST_VALUE, the window frame doesn't get in the way here: the window always starts at the first row. So the “last” value is easier to get as FIRST_VALUE with the order reversed.

You can't use the result in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

LAST_VALUE NTH_VALUE LAG

Learn more in the course

FIRST_VALUE and LAST_VALUE