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!
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] )
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.
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);
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;
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;
So here we calculated the highest marks in every Field.
In this tutorial, we studied the First_Value() function in MySQL. For more reference, check out the official documentation of MySQL.