In this tutorial, we will study MySQL First_Value() function. Without further ado, let’s get started.
Introduction to MySQL First_Value Function
As the name suggests, the MySQL First_Value function allows us to select the first value from a given column. It is useful when we want to retrieve the topmost row from a column or select a row based on some condition. The First_Value function can be used in various scenarios such as retrieving the first row of a table, finding the first non-NULL value in a column, etc.
In MySQL, the First_Value() function is used to select the first row of a window frame, partition, or result set. The syntax for the following is-
First_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 of MySQL First_Value()
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)
MySQL First_Value function over the result set
Now here we will find out which Name has the least marks. The code for the following is-
Select Name, marks, First_Value(Name) Over ( Order By marks ) least_marks From detail;Code language: SQL (Structured Query Language) (sql)
Here we can see that John has the least marks in every Field.
First_Value function in partition
Now we will see which Name has the least marks in every Field. The code for the following is-
Select Name, marks, Field, First_Value(Name) Over ( Partition By Field Order By marks ) least_Marks From detail;Code language: SQL (Structured Query Language) (sql)
So here we calculated the least 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.