SQL FIRST_VALUE

Summary: in this tutorial, you will learn how to use the SQL FIRST_VALUE() function to return the first value in an ordered set of values.

Overview of FIRST_VALUE() function

The FIRST_VALUE() is a window function that returns the first value in an ordered set of values.

The following illustrates the syntax of the FIRST_VALUE() function:

FIRST_VALUE(expression) OVER (
    partition_clause
    order_clause
    frame_clause
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

 expression

The return value of the expression from the first row in a partition or result set.

The OVER clause consists of three clauses: partition_clause, order_clause, and frame_clause.

partition_clause

The partition_clause clause has the following syntax:

PARTITION BY expr1, expr2, ...
Code language: SQL (Structured Query Language) (sql)

The PARTITION BY clause divides the rows of the result sets into partitions to which the FIRST_VALUE() function applies. If you skip the PARTITION BY clause, the function treats the whole result set as a single partition.

order_clause

The order_clause clause sorts the rows in partitions to which the FIRST_VALUE() function applies. The ORDER BY clause has the following syntax:

ORDER BY expr1 [ASC | DESC], expr2, ...
Code language: SQL (Structured Query Language) (sql)

frame_clause

The frame_clause defines the subset (or frame) of the current partition. Check it out the window function tutorial for the detailed information of the frame clause.

SQL FIRST_VALUE() function examples

We will use the employees and departments tables from the sample database to demonstrate the FIRST_VALUE() function:

A) Using SQL FIRST_VALUE() function over result set example

The following statement finds the employee who has the lowest salary in the company:

SELECT
    first_name,
    last_name,
    salary,
    FIRST_VALUE (first_name) OVER (
        ORDER BY salary
    ) lowest_salary
FROM
    employees e;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this example, the ORDER BY clause sorted the employees by salary and the FIRST_VALUE() selected the first name of the employee who has the lowest salary.

B) Using SQL FIRST_VALUE() over partition example

The following statement returns the employees who have the lowest salary in each department.

SELECT
    first_name,
    last_name,
    department_name,
    salary,
    FIRST_VALUE (CONCAT(first_name,' ',last_name)) OVER (
        PARTITION BY department_name
        ORDER BY salary
    ) lowest_salary
FROM
    employees e
    INNER JOIN departments d 
        ON d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

In this example:

• First, the PARTITION BY clause divided the employees by departments.
• Then, the ORDER BY clause sorted employees in each department by their salary from low to high.
• Finally, the FIRST_VALUE() is applied to sorted rows in each partition. It selected the employee who has the lowest salary per department.

In this tutorial, you have learned how to use the SQL FIRST_VALUE() function to get first value in an ordered set of values.