SQL FIRST_VALUE Function

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

Overview of SQL FIRST_VALUE() function #

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

Here’s 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 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:

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)

Try it

Output:

 first_name  |  last_name  |  salary  | lowest_salary
-------------+-------------+----------+---------------
 Karen       | Colmenares  |  2500.00 | Karen
 Guy         | Himuro      |  2600.00 | Karen
 Irene       | Mikkilineni |  2700.00 | Karen
 Sigal       | Tobias      |  2800.00 | Karen
 Shelli      | Baida       |  2900.00 | Karen
 Alexander   | Khoo        |  3100.00 | Karen
...

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.

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)

Try it

Output:

 first_name  |  last_name  | department_name  |  salary  |   lowest_salary
-------------+-------------+------------------+----------+-------------------
 William     | Gietz       | Accounting       |  8300.00 | William Gietz
 Shelley     | Higgins     | Accounting       | 12000.00 | William Gietz
 Jennifer    | Whalen      | Administration   |  4400.00 | Jennifer Whalen
 Lex         | De Haan     | Executive        | 17000.00 | Lex De Haan
 Neena       | Kochhar     | Executive        | 17000.00 | Lex De Haan
 Steven      | King        | Executive        | 24000.00 | Lex De Haan
 Luis        | Popp        | Finance          |  6900.00 | Luis Popp
 Ismael      | Sciarra     | Finance          |  7700.00 | Luis Popp
 Jose Manuel | Urman       | Finance          |  7800.00 | Luis Popp
 John        | Chen        | Finance          |  8200.00 | Luis Popp
 Daniel      | Faviet      | Finance          |  9000.00 | Luis Popp
 Nancy       | Greenberg   | Finance          | 12000.00 | Luis Popp
...

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.

Databases #

Was this tutorial helpful ?