Example 1
Show each employee's salary alongside the top earner in their department
FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) evaluates separately for each department. Within Eng, the window is ordered from highest salary to lowest, so FIRST_VALUE returns Dave (salary 100000) for every row in that partition. Within Sales, Carol (90000) is first so all three Sales rows show Carol as top earner. To find the lowest earner instead, use LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — the explicit frame clause is required to make LAST_VALUE reach the true end of the partition.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE employees (
id INT,
dept VARCHAR(20),
name VARCHAR(50),
salary INT
);
INSERT INTO
employees (id, dept, name, salary)
VALUES
(1, 'Sales', 'Alice', 80000),
(2, 'Sales', 'Bob', 60000),
(3, 'Sales', 'Carol', 90000),
(4, 'Eng', 'Dave', 100000),
(5, 'Eng', 'Eve', 95000);Validated query Shared across supported engines.
SQL
SELECT
dept,
name,
salary,
FIRST_VALUE (name) OVER (
PARTITION BY
dept
ORDER BY
salary DESC
) AS top_earner
FROM
employees
ORDER BY
dept,
salary DESC;Expected result Returned rows for the shared example.
| dept | name | salary | top_earner |
|---|---|---|---|
| Eng | Dave | 100000 | Dave |
| Eng | Eve | 95000 | Dave |
| Sales | Carol | 90000 | Carol |
| Sales | Alice | 80000 | Carol |
| Sales | Bob | 60000 | Carol |
The syntax is identical across all supported engines.