{"id":7052,"date":"2018-08-28T08:13:07","date_gmt":"2018-08-28T15:13:07","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7052"},"modified":"2024-01-04T06:45:48","modified_gmt":"2024-01-04T13:45:48","slug":"mysql-last_value-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-last_value-function\/","title":{"rendered":"MySQL LAST_VALUE Function"},"content":{"rendered":"\n

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

MySQL LAST_VALUE()\u00a0Function Overview<\/h2>\n\n\n\n

The LAST_VALUE()<\/code> function is a window function<\/a> that allows you to select the last row in an ordered set of rows.<\/p>\n\n\n\n

The following shows the syntax of the LAST_VALUE()<\/code> function:<\/p>\n\n\n

LAST_VALUE (expression) OVER (\n   [partition_clause]\n   [order_clause]\n   [frame_clause]\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The LAST_VALUE()<\/code> function returns the value of the expression<\/code> from the last row of a sorted set of rows.<\/p>\n\n\n\n
The OVER<\/code> clause has three clauses: partition_clause<\/code>, order_clause<\/code>, and frame_clause<\/code>.<\/p>\n\n\n\n
partition_clause<\/h3>\n\n\n\n
The partition_clause<\/code> has the following syntax:<\/p>\n\n\n
PARTITION BY expr1, expr2, ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The PARTITION BY<\/code> clause distributes the result sets into multiple partitions specified by one or more expressions expr1<\/code>, expr2<\/code>, etc. The LAST_VALUE()<\/code> function is applied to each partition independently.<\/p>\n\n\n\n
order_clause<\/h3>\n\n\n\n
The order_clause<\/code> has the following syntax:<\/p>\n\n\n
ORDER BY  expr1 [ASC|DESC],...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The ORDER BY<\/code> clause specifies the logical orders of the rows in the partitions on which the LAST_VALUE()<\/code> function operates.<\/p>\n\n\n\n
MySQL LAST_VALUE() function examples<\/h2>\n\n\n\n
Let’s set up a sample table for demonstration.<\/p>\n\n\n\n
The following is the script to create the overtime<\/code> table and populate data into the table.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> overtime (\n    employee_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    department VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    hours<\/span> INT<\/span> NOT<\/span> NULL<\/span>,\n     PRIMARY KEY<\/span> (employee_name , department)\n);\nINSERT<\/span> INTO<\/span> overtime(employee_name, department, hours<\/span>)\nVALUES<\/span>('Diane Murphy'<\/span>,'Accounting'<\/span>,37<\/span>),\n('Mary Patterson'<\/span>,'Accounting'<\/span>,74<\/span>),\n('Jeff Firrelli'<\/span>,'Accounting'<\/span>,40<\/span>),\n('William Patterson'<\/span>,'Finance'<\/span>,58<\/span>),\n('Gerard Bondur'<\/span>,'Finance'<\/span>,47<\/span>),\n('Anthony Bow'<\/span>,'Finance'<\/span>,66<\/span>),\n('Leslie Jennings'<\/span>,'IT'<\/span>,90<\/span>),\n('Leslie Thompson'<\/span>,'IT'<\/span>,88<\/span>),\n('Julie Firrelli'<\/span>,'Sales'<\/span>,81<\/span>),\n('Steve Patterson'<\/span>,'Sales'<\/span>,29<\/span>),\n('Foon Yue Tseng'<\/span>,'Sales'<\/span>,65<\/span>),\n('George Vanauf'<\/span>,'Marketing'<\/span>,89<\/span>),\n('Loui Bondur'<\/span>,'Marketing'<\/span>,49<\/span>),\n('Gerard Hernandez'<\/span>,'Marketing'<\/span>,66<\/span>),\n('Pamela Castillo'<\/span>,'SCM'<\/span>,96<\/span>),\n('Larry Bott'<\/span>,'SCM'<\/span>,100<\/span>),\n('Barry Jones'<\/span>,'SCM'<\/span>,65<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
1) Using MySQL LAST_VALUE() function over the whole query result example<\/h3>\n\n\n\n
The following statement gets the employee name, overtime, and the employee who has the highest overtime:<\/p>\n\n\n
SELECT<\/span>\n    employee_name,\n    hours<\/span>,\n    LAST_VALUE<\/span>(employee_name) OVER<\/span> (\n        ORDER<\/span> BY<\/span> hours<\/span>\n        RANGE<\/span> BETWEEN<\/span>\n            UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span>\n            UNBOUNDED<\/span> FOLLOWING<\/span>\n    ) highest_overtime_employee\nFROM<\/span>\n    overtime;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, the ORDER BY<\/code> clause specified the logical order of rows in the result set by hours from low to high.<\/p>\n\n\n\n
The default frame specification is as follows:<\/p>\n\n\n
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It means that the frame starts at the first row and ends at the current row of the result set.<\/p>\n\n\n\n
Therefore, to get the employee who has the highest overtime, we changed the frame specification to the following:<\/p>\n\n\n
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This indicates that the frame starts at the first row and ends at the last row of the result set.<\/p>\n\n\n\n
2) Using MySQL LAST_VALUE() function over partitions example<\/h3>\n\n\n\n
The following statement finds the employee who has the highest overtime in each department:<\/p>\n\n\n
SELECT<\/span>\n    employee_name,\n    department,\n    hours<\/span>,\n    LAST_VALUE<\/span>(employee_name) OVER<\/span> (\n\t\tPARTITION<\/span> BY<\/span> department\n        ORDER<\/span> BY<\/span> hours<\/span>\n        RANGE<\/span> BETWEEN<\/span>\n\t    UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span>\n            UNBOUNDED<\/span> FOLLOWING<\/span>\n\t) most_overtime_employee\nFROM<\/span>\n    overtime;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, first, the PARTITION BY<\/code> clause divided the employees by departments. Then, the ORDER BY<\/code> clause orders the employees in each department by overtime from low to high.<\/p>\n\n\n\n
The frame specification in this case is the whole partition. As a result, the LAST_VALUE()<\/code> function picked the last row in each partition which was the employee who had the highest overtime.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL LAST_VALUE()<\/code> function to get the last row in an ordered set of rows.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
\u00a0frame_clause<\/h3>\n\n\n\n
The frame_clause<\/code> defines the subset of the current partition to which the LAST_VALUE()<\/code> function applies. For more detailed information on the frame_clause<\/code>, please check out the window functions tutorial<\/a>.<\/p>\n\n\n\n