MySQL Last_Value Function – Beginner’s Guide

By /
Last Value Function In MySQL

In this article, I will be introducing you to the Last_Value function in MySQL. This function is used for getting the last value of a column in an ordered set query. Let’s move ahead and learn more about it!

Also read: MySQL First_Value Function – Beginner’s Guide

Introduction to MySQL Last_Value() function

The Last_Value function is one of the Window Functions in MySQL. It allows you to get the last value in a set of values. This function is useful when you want to find out the most recent value in a set of data. In MySQL, the Last_Value() function is used to select the last row of a window frame, partition, or result set. The syntax for the following is-

Last_Value (expression) OVER (
   [partition_clause]
   [order_clause]
   [frame_clause]
)Code language: SQL (Structured Query Language) (sql)

Here in the syntax-

  • expression – This is the value which First_Value() function returns.
  • Over – This consists of three clauses which are partition_clause, order_clause, and frame_clause.
  • partition_clause – The partition clause is used to divide the rows of the result set into partitions and then the function is applied to them.
  • order_clause – The order clause specifies the order in which the function is applied.
  • frame_clause – The frame clause specifies the frame of the current partition.

Example

First, we will create a table named detail and insert records into it. The code for the following is-

Create Table detail (
Name varchar(100) Not Null,
Field varchar (100) Not Null,
marks int Not Null,
Primary Key (Name, Field)
);
 
Insert Into detail(Name, Field, marks)
Values('Peter', 'Bio', 44),
('John', 'Bio', 28),
('Jade', 'Bio', 55),
('Chuck', 'Sales', 88),
('Morphy', 'Sales', 67),
('Lily', 'Computer', 78),
('Daisy', 'Computer', 89),
('Steve', 'Finance', 95),
('Tia', 'Finance', 88),
('Jones', 'Finance', 65);Code language: SQL (Structured Query Language) (sql)

Last_Value function over the result set

Now here we will find out which Name has the highest marks. The code for the following is-

Select
Name,
marks,
Last_Value(Name) Over (
Order By marks
Range Between
Unbounded Preceding and
Unbounded Following
) highest_marks
From
detail;Code language: SQL (Structured Query Language) (sql)

Output-

Last Value
Last Value

Here we can see that Steve has the highest marks in every Field. The Range Between Unbounded Preceding and Unbounded Following means that the frame starts at the first row and ends at the last row of the result set.

Last_Value function in partition

Now we will see which Name has the highest marks in every Field. The code for the following is-

Select
Name,
Field,
marks,
Last_Value(Name) Over (
Partition By Field
Order By marks
Range Between
Unbounded Preceding and
Unbounded Following
) highest
From
detail;Code language: SQL (Structured Query Language) (sql)

Output-

Highest Marks
Highest Marks

So here we calculated the highest marks in every Field.

Conclusion

In this tutorial, we studied the First_Value() function in MySQL. For more reference, check out the official documentation of MySQL.