MySQL LAST_VALUE Function
Summary: in this tutorial, you will learn how to use the MySQL LAST_VALUE() function to return the last row in an ordered set of rows.
MySQL LAST_VALUE() Function Overview
The LAST_VALUE() function is a window function that allows you to select the last row in an ordered set of rows.
The following shows the syntax of the LAST_VALUE() function:
LAST_VALUE (expression) OVER (
[partition_clause]
[order_clause]
[frame_clause]
)Code language: SQL (Structured Query Language) (sql)
The LAST_VALUE() function returns the value of the expression from the last row of a sorted set of rows.
The OVER clause has three clauses: partition_clause, order_clause, and frame_clause.
partition_clause
The partition_clause has the following syntax:
PARTITION BY expr1, expr2, ...Code language: SQL (Structured Query Language) (sql)
The PARTITION BY clause distributes the result sets into multiple partitions specified by one or more expressions expr1, expr2, etc. The LAST_VALUE() function is applied to each partition independently.
order_clause
The order_clause has the following syntax:
ORDER BY expr1 [ASC|DESC],...Code language: SQL (Structured Query Language) (sql)
The ORDER BY clause specifies the logical orders of the rows in the partitions on which the LAST_VALUE() function operates.
frame_clause
The frame_clause defines the subset of the current partition to which the LAST_VALUE() function applies. For more detailed information on the frame_clause, please check it out the window functions tutorial.
MySQL LAST_VALUE() function examples
Let’s setup a sample table for demonstration.
The following is the script to create the overtime table and populates data into the table.
CREATE TABLE overtime (
employee_name VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
hours INT NOT NULL,
PRIMARY KEY (employee_name , department)
);
INSERT INTO overtime(employee_name, department, hours)
VALUES('Diane Murphy','Accounting',37),
('Mary Patterson','Accounting',74),
('Jeff Firrelli','Accounting',40),
('William Patterson','Finance',58),
('Gerard Bondur','Finance',47),
('Anthony Bow','Finance',66),
('Leslie Jennings','IT',90),
('Leslie Thompson','IT',88),
('Julie Firrelli','Sales',81),
('Steve Patterson','Sales',29),
('Foon Yue Tseng','Sales',65),
('George Vanauf','Marketing',89),
('Loui Bondur','Marketing',49),
('Gerard Hernandez','Marketing',66),
('Pamela Castillo','SCM',96),
('Larry Bott','SCM',100),
('Barry Jones','SCM',65);
Code language: SQL (Structured Query Language) (sql)
1) MySQL LAST_VALUE() over the whole query result example
The following statement gets the employee name, overtime, and the employee who has the highest overtime:
SELECT
employee_name,
hours,
LAST_VALUE(employee_name) OVER (
ORDER BY hours
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
) highest_overtime_employee
FROM
overtime;Code language: SQL (Structured Query Language) (sql)
The output is:
In this example, the ORDER BY clause specified the logical order of rows in the result set by hours from low to high.
The default frame specification is as follows:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWCode language: SQL (Structured Query Language) (sql)
It means that the frame starts at the first row and ends at the current row of the result set.
Therefore, to get the employee who has the highest overtime, we changed the frame specification to the following:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGCode language: SQL (Structured Query Language) (sql)
This indicates that the frame starts at the first row and ends at the last row of the result set.
2) MySQL LAST_VALUE() over the partition example
The following statement finds the employee who has the highest overtime in each department:
SELECT
employee_name,
department,
hours,
LAST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY hours
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
) most_overtime_employee
FROM
overtime;Code language: SQL (Structured Query Language) (sql)
The following picture shows the output:
In this example, first, the PARTITION BY clause divided the employees by departments. Then, the ORDER BY clause orders the employees in each department by overtime from low to high.
The frame specification in this case is the whole partition. As a result, the LAST_VALUE() function picked the last row in each partition which was the employee who has the highest overtime.
In this tutorial, you have learned how to use the MySQL LAST_VALUE() function to get the last row in an ordered set of rows.