MySQL LAST_VALUE Function

 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() the 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]
)

The LAST_VALUE() the function returns the value of the expression from the last row of a sorted set of rows.

The OVER the clause has three clauses: partition_clause, order_clause, and frame_clause.

partition_clause

The partition_clause has the following syntax:

PARTITION BY expr1, expr2, ...

The PARTITION BY clause distributes the result sets into multiple partitions specified by one or more expressions expr1, expr2, etc. The LAST_VALUE() the function is applied to each partition independently.

order_clause

The order_clause has the following syntax:

ORDER BY  expr1 [ASC|DESC],...

The ORDER BY the 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 out the window functions tutorial.

MySQL LAST_VALUE() function examples

Let’s set up a sample table for demonstration.

The following is the script to create the overtime table and populate 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);

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;

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 ROW

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 FOLLOWING

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 partitioning 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;

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.