MySQL 8 Cookbook

First, last, and nth values

You can select the first, last, and nth values in the window results. If the row does not exist, a NULL value is returned.

Suppose you want to find the first, last, and third values from the window:

mysql> SELECT hire_date_year, salary, RANK() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'first', NTH_VALUE(salary, 3) OVER w AS 'third', LAST_VALUE(salary) OVER w AS 'last' FROM employees join salaries ON salaries.emp_no=employees.emp_no WINDOW w AS (PARTITION BY hire_date_year ORDER BY salary DESC) ORDER BY salary DESC LIMIT 10;+----------------+--------+------+--------+--------+--------+| hire_date_year | salary | Rank | first  | third  | last   |+----------------+--------+------+--------+--------+--------+| 1985 ...