{"id":1765,"date":"2018-09-09T01:56:08","date_gmt":"2018-09-09T08:56:08","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1765"},"modified":"2025-02-04T07:06:54","modified_gmt":"2025-02-04T14:06:54","slug":"sql-last_value","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-last_value\/","title":{"rendered":"SQL LAST_VALUE Function"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL LAST_VALUE()<\/code> function to return the last value in an ordered set of values.<\/p>\r\n\r\n\r\n\r\n

Overview of the SQL LAST_VALUE() Function #<\/a><\/h2>\r\n\r\n\r\n\r\n

The LAST_VALUE()<\/code> is a window function that returns the last value in an ordered set of values.<\/p>\r\n\r\n\r\n\r\n

Here’s the basic syntax of the LAST_VALUE()<\/code> function:<\/p>\r\n\r\n\r\n

LAST_VALUE(expression) OVER (\r\n    partition_clause\r\n    order_clause\r\n    frame_clause\r\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this syntax:<\/p>\r\n\r\n\r\n\r\n
expression #<\/a><\/h3>\r\n\r\n\r\n\r\n
The returned value of the function can be a column or an expression that results in a single value.<\/p>\r\n\r\n\r\n\r\n
The OVER<\/code> clause consists of three clauses: partition_clause<\/code>, order_clause<\/code>, and frame_clause<\/code>.<\/p>\r\n\r\n\r\n\r\n
partition_clause #<\/a><\/h3>\r\n\r\n\r\n\r\n
The syntax of the partition_clause<\/code> clause is as follows:<\/p>\r\n\r\n\r\n
PARTITION BY expr1, expr2, ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The PARTITION BY<\/code> clause divides the rows of the result sets into partitions to which the LAST_VALUE()<\/code> function applies. Because the PARTITION BY<\/code> clause is optional, if you omit it, the function treats the whole result set as a single partition.<\/p>\r\n\r\n\r\n\r\n
order_clause #<\/a><\/h3>\r\n\r\n\r\n\r\n
The order_clause<\/code> clause specified the order of rows in partitions to which the LAST_VALUE()<\/code> function applies. The syntax of the ORDER BY<\/code> clause is as follows:<\/p>\r\n\r\n\r\n
ORDER BY expr1 [ASC | DESC], expr2, ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQL LAST_VALUE() function examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We will use the following employees<\/code> and departments<\/code> tables from the sample database<\/a> for demonstration.<\/p>\r\n\r\n\r\n\r\n
\"Employees<\/figure>\r\n\r\n\r\n\r\n
Using SQL LAST_VALUE() function over the result set example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement finds employees who have the highest salary in the company:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n    first_name,\r\n    last_name,\r\n    salary,\r\n    LAST_VALUE<\/span> (first_name) OVER<\/span> (\r\n        ORDER<\/span> BY<\/span> salary\r\n        RANGE<\/span> BETWEEN<\/span> UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span> \r\n        UNBOUNDED<\/span> FOLLOWING<\/span>\r\n    ) highest_salary\r\nFROM<\/span>\r\n    employees;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Here is the partial output:<\/p>\r\n\r\n\r\n\r\n
In this example, the ORDER BY<\/code> clause sorted employees by salary and the LAST_VALUE()<\/code> selected the first name of the employee who has the lowest salary.<\/p>\r\n\r\n\r\n\r\n
The frame clause is as follows:<\/p>\r\n\r\n\r\n
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It means the frame starts at the first row (UNBOUNDED PRECEDING<\/code>) and ends at the last row ( UNBOUNDED FOLLOWING<\/code>) of the result set.<\/p>\r\n\r\n\r\n\r\n
Using SQL LAST_VALUE() over partition example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement finds employees who have the highest salary in each department.<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n    first_name,\r\n    last_name,\r\n    department_name,\r\n    salary,\r\n    LAST_VALUE<\/span> (CONCAT<\/span>(first_name,' '<\/span>,last_name)) OVER<\/span> (\r\n        PARTITION<\/span> BY<\/span> department_name\r\n        ORDER<\/span> BY<\/span> salary\r\n        RANGE<\/span> BETWEEN<\/span> UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span> \r\n        UNBOUNDED<\/span> FOLLOWING<\/span>\r\n    ) highest_salary\r\nFROM<\/span>\r\n    employees e\r\n    INNER<\/span> JOIN<\/span> departments d \r\n        ON<\/span> d.department_id = e.department_id;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following picture shows the output of the query:<\/p>\r\n\r\n\r\n\r\n
\"SQL<\/figure>\r\n\r\n\r\n\r\n
Let’s examine the query in more detail:<\/p>\r\n\r\n\r\n\r\n
frame_clause #<\/a><\/h3>\r\n\r\n\r\n\r\n
The frame_clause<\/code> defines the subset (or frame) of the partition being evaluated. For detailed information on the frame clause, check out the\u00a0window function tutorial<\/a>.<\/p>\r\n\r\n\r\n\r\n