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 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);
Code language: SQL (Structured Query Language) (sql)

1) Using MySQL LAST_VALUE() function 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) Using MySQL LAST_VALUE() function over partitions 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 had the highest overtime.

Summary

• Use the MySQL LAST_VALUE() function to get the last row in an ordered set of rows.