MySQL First_Value Function – Beginner’s Guide

By /
First Value Function In MySQL

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)

Output-

First Value
First Value

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)

Output-

First Value
First Value

So here we calculated the least 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.