LAST_VALUE Function - SQL
Overview
The LAST_VALUE function in SQL returns the last value in an ordered set of values within a partition. It is useful for retrieving the last entry in a specified order for each partition of a result set.
Example:
SELECT department_id, first_name, salary,
LAST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM company.employees
Syntax:
SELECT column_name,
LAST_VALUE(expression) OVER (PARTITION BY partition_column[s] ORDER BY order_column[s] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
LAST_VALUE(expression) retrieves the last specified expression value based on the specified order (e.g., by hire date) within each partition.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures that the window frame includes all rows in the partition.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a LAST_VALUE column to the last employee within their department based on descending salary order. Gustavo is the last employee in department_id 1 based on his salary, his name is repeated 3 times since there are 3 results from department_id 1. The same goes for James except he is the lowest salary in department_id 2.
Example: Query with the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column of employees in department_id 1, along with a LAST_VALUE column to the last employee within this department based on descending salary order. Gustavo is the last employee in department_id 1 based on his salary, his name is repeated 3 times since there are 3 results from department_id 1.
