SQL LAST_VALUE Function

Summary: in this tutorial, you will learn how to use the SQL LAST_VALUE() function to return the last value in an ordered set of values.

Overview of the SQL LAST_VALUE() Function #

The LAST_VALUE() is a window function that returns the last value in an ordered set of values.

Here’s the basic syntax of the LAST_VALUE() function:

LAST_VALUE(expression) OVER ( partition_clause order_clause frame_clause )Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression #

The returned value of the function can be a column or an expression that results in a single value.

The OVER clause consists of three clauses: partition_clause, order_clause, and frame_clause.

partition_clause #

The syntax of the partition_clause clause is as follows:

PARTITION BY expr1, expr2, ...Code language: SQL (Structured Query Language) (sql)

The PARTITION BY clause divides the rows of the result sets into partitions to which the LAST_VALUE() function applies. Because the PARTITION BY clause is optional, if you omit it, the function treats the whole result set as a single partition.

order_clause #

The order_clause clause specified the order of rows in partitions to which the LAST_VALUE() function applies. The syntax of the ORDER BY clause is as follows:

ORDER BY expr1 [ASC | DESC], expr2, ...Code language: SQL (Structured Query Language) (sql)

frame_clause #

The frame_clause defines the subset (or frame) of the partition being evaluated. For detailed information on the frame clause, check out the window function tutorial.

SQL LAST_VALUE() function examples #

We will use the following employees and departments tables from the sample database for demonstration.

Employees & Departments Tables

Using SQL LAST_VALUE() function over the result set example #

The following statement finds employees who have the highest salary in the company:

SELECT first_name, last_name, salary, LAST_VALUE (first_name) OVER ( ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) highest_salary FROM employees; Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this example, the ORDER BY clause sorted employees by salary and the LAST_VALUE() selected the first name of the employee who has the lowest salary.

The frame clause is as follows:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Code language: SQL (Structured Query Language) (sql)

It means the frame starts at the first row (UNBOUNDED PRECEDING) and ends at the last row ( UNBOUNDED FOLLOWING) of the result set.

Using SQL LAST_VALUE() over partition example #

The following statement finds employees who have the highest salary in each department.

SELECT first_name, last_name, department_name, salary, LAST_VALUE (CONCAT(first_name,' ',last_name)) OVER ( PARTITION BY department_name ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) highest_salary FROM employees e INNER JOIN departments d ON d.department_id = e.department_id; Code language: SQL (Structured Query Language) (sql)

The following picture shows the output of the query:

SQL LAST_VALUE Function Over Partition Example

Let’s examine the query in more detail:

  • First, the PARTITION BY clause divided the employees by departments.
  • Then, the ORDER BY clause sorted employees in each department by their salary in ascending order.
  • Finally, the LAST_VALUE() is applied to sorted rows in each partition. Because the frame starts at the first row and ends at the last row of each partition, the LAST_VALUE() selected the employee who has the highest salary.

In this tutorial, you have learned how to use the SQL LAST_VALUE() function to get the last value in an ordered set of values.

Databases #

Was this tutorial helpful ?