Keyword
MAX_BY and MIN_BY
The value of one column on the row where another column is largest (or smallest), in a plain
GROUP BY.MAX(salary) gives the amount, not the person; MAX_BY(first_name, salary) gives the name. Two MINs in one
GROUP BY can come from different rows, so "the page of the first event" is MIN_BY(page, occurred_at). On a tie,
MAX_BY keeps any of the tied rows: when ties matter, rank with ROW_NUMBER() OVER (… ORDER BY salary DESC, employee_id)
and keep row 1. Snowflake, BigQuery, Databricks and Trino spell it MAX_BY / MIN_BY, DuckDB also arg_max /
arg_min; Postgres has neither: use ROW_NUMBER there.
The trap
SELECT job_title, MAX(salary) AS top_salary, MAX(first_name) AS best_paid
FROM employees
WHERE job_title IN ('Order picker', 'Customer advisor', 'Buyer')
GROUP BY job_title
ORDER BY job_title;
| job_title | top_salary | best_paid |
|---|---|---|
| Buyer | 3100.00 | Nadia |
| Customer advisor | 2450.00 | Thomas |
| Order picker | 2050.00 | Océane |
The fix
SELECT job_title, MAX(salary) AS top_salary,
MAX_BY(first_name, salary) AS best_paid
FROM employees
WHERE job_title IN ('Order picker', 'Customer advisor', 'Buyer')
GROUP BY job_title
ORDER BY job_title;
| job_title | top_salary | best_paid |
|---|---|---|
| Buyer | 3100.00 | Nadia |
| Customer advisor | 2450.00 | Amélie |
| Order picker | 2050.00 | Laura |