FIRST_VALUE / LAST_VALUEFunctions
Buy Me A Coffee

Window functions that return the first or last value in the current window frame. Commonly used to carry forward the first value in a group or compare each row to the group's boundary.

Returns the first or last value from an ordered window frame. Useful for finding the first/last occurrence per group, carrying forward values, and comparing against an endpoint. LAST_VALUE needs an explicit frame to be meaningful.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 FIRST_VALUE() and LAST_VALUE() added in MySQL 8.0. Default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — this means LAST_VALUE() will usually return the current row's value, not the last in the partition. Always specify the frame explicitly for LAST_VALUE.
PostgreSQL ✓ Supported 8.4 FIRST_VALUE() and LAST_VALUE() supported. Same default frame gotcha as MySQL — LAST_VALUE needs explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the true last value in the partition.
SQL Server ✓ Supported 2012 FIRST_VALUE() and LAST_VALUE() added in SQL Server 2012. Same default frame gotcha. IGNORE NULLS option added in SQL Server 2022.
Oracle ✓ Supported 8i FIRST_VALUE() and LAST_VALUE() supported. IGNORE NULLS / RESPECT NULLS options available (Oracle supports these earlier than other engines). Same default frame gotcha with LAST_VALUE.
SQLite ✓ Supported 3.25.0 FIRST_VALUE() and LAST_VALUE() added in SQLite 3.25.0. Standard syntax. Same default frame gotcha with LAST_VALUE.

Details

LAST_VALUE's default frame (RANGE UNBOUNDED PRECEDING TO CURRENT ROW) is a universal gotcha — it almost always returns the current row's own value, not the partition's last value. Always explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using LAST_VALUE. FIRST_VALUE does not have this problem because the first row is always in frame.

Standard Syntax

SELECT name, dept, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_in_dept, LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_in_dept FROM employees;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2012 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

FIRST_VALUE() and LAST_VALUE() added in MySQL 8.0. Default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — this means LAST_VALUE() will usually return the current row's value, not the last in the partition. Always specify the frame explicitly for LAST_VALUE.

SELECT name, dept, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary, -- Must specify UNBOUNDED FOLLOWING for LAST_VALUE to work as expected: LAST_VALUE(salary) OVER ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS bottom_salary FROM employees;

PostgreSQL

FIRST_VALUE() and LAST_VALUE() supported. Same default frame gotcha as MySQL — LAST_VALUE needs explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the true last value in the partition.

SELECT name, dept, salary, FIRST_VALUE(salary) OVER w AS top_salary, LAST_VALUE(salary) OVER w AS bottom_salary FROM employees WINDOW w AS ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING );

SQL Server

FIRST_VALUE() and LAST_VALUE() added in SQL Server 2012. Same default frame gotcha. IGNORE NULLS option added in SQL Server 2022.

SELECT name, dept, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary, LAST_VALUE(salary) OVER ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS bottom_salary FROM employees; -- SQL Server 2022+: SELECT FIRST_VALUE(salary) IGNORE NULLS OVER (ORDER BY hire_date) FROM employees;

Oracle

FIRST_VALUE() and LAST_VALUE() supported. IGNORE NULLS / RESPECT NULLS options available (Oracle supports these earlier than other engines). Same default frame gotcha with LAST_VALUE.

SELECT name, dept, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary, LAST_VALUE(salary) OVER ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS bottom_salary, FIRST_VALUE(salary) IGNORE NULLS OVER (ORDER BY hire_date) AS first_non_null FROM employees;

SQLite

FIRST_VALUE() and LAST_VALUE() added in SQLite 3.25.0. Standard syntax. Same default frame gotcha with LAST_VALUE.

SELECT name, dept, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary, LAST_VALUE(salary) OVER ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS bottom_salary FROM employees;