Introduction to the FIRST_VALUE() Function in MySQL

In MySQL, the FIRST_VALUE() function is a window function that returns the value of the given expression from the first row of the window frame.

Syntax

The syntax goes like this:

FIRST_VALUE(expr) [null_treatment] over_clause

So it requires an OVER clause, which determines how the rowset is partitioned and ordered before the window function is applied.

The optional null_treatment clause specifies how NULL values are treated. This clause is part of the SQL standard, but MySQL only allows RESPECT NULLS, which is also the default. RESPECT NULLS means that NULL values are considered when calculating results. On the other hand, IGNORE NULLS is parsed, but produces an error (at least as of MySQL 8.0.33).

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    DogName,
    Activity,
    Score,
    FIRST_VALUE( Score ) 
        OVER ( ORDER BY Score ROWS UNBOUNDED PRECEDING ) 
    AS "First Value"
FROM Dogs;

Result:

+---------+-------------+-------+-------------+
| DogName | Activity    | Score | First Value |
+---------+-------------+-------+-------------+
| Bruno   | Keep Quiet  |     1 |           1 |
| Cooper  | Keep Quiet  |     8 |           1 |
| Max     | Keep Quiet  |    12 |           1 |
| Bruno   | Fetch Stick |    43 |           1 |
| Bruno   | Wag Tail    |    51 |           1 |
| Cooper  | Wag Tail    |    51 |           1 |
| Cooper  | Fetch Stick |    67 |           1 |
| Max     | Wag Tail    |    87 |           1 |
| Max     | Fetch Stick |    91 |           1 |
+---------+-------------+-------+-------------+
9 rows in set (0.00 sec)

We can see that the First Value column contains the same value throughout the result set. That’s because there’s only one window frame, and so this value applies to all rows.

Partitioning

Let’s add a PARTITION BY clause to see how that affects the result:

SELECT 
    DogName,
    Activity,
    Score,
    FIRST_VALUE( Score ) OVER win AS "First Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+-------------+
| DogName | Activity    | Score | First Value |
+---------+-------------+-------+-------------+
| Bruno   | Fetch Stick |    43 |          43 |
| Cooper  | Fetch Stick |    67 |          43 |
| Max     | Fetch Stick |    91 |          43 |
| Bruno   | Keep Quiet  |     1 |           1 |
| Cooper  | Keep Quiet  |     8 |           1 |
| Max     | Keep Quiet  |    12 |           1 |
| Bruno   | Wag Tail    |    51 |          51 |
| Cooper  | Wag Tail    |    51 |          51 |
| Max     | Wag Tail    |    87 |          51 |
+---------+-------------+-------+-------------+
9 rows in set (0.00 sec)

This time we get a different result. We’ve partitioned the results by activity, which in turn has caused the FIRST_VALUE() function to produce a new value for each partition. So we can see that 43 is the first value of the first partition, 1 is the first value of the second partition, and 51 is the first value of the third partition.

The `null_treatment` Clause

As mentioned, the optional null_treatment clause specifies how to treat NULL values. The default value for this clause is RESPECT NULLS. Therefore, the above example implicitly uses RESPECT NULLS.

Let’s explicitly specify RESPECT NULLS:

SELECT 
    DogName,
    Activity,
    Score,
    FIRST_VALUE( Score ) RESPECT NULLS OVER win AS "First Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+-------------+
| DogName | Activity    | Score | First Value |
+---------+-------------+-------+-------------+
| Bruno   | Fetch Stick |    43 |          43 |
| Cooper  | Fetch Stick |    67 |          43 |
| Max     | Fetch Stick |    91 |          43 |
| Bruno   | Keep Quiet  |     1 |           1 |
| Cooper  | Keep Quiet  |     8 |           1 |
| Max     | Keep Quiet  |    12 |           1 |
| Bruno   | Wag Tail    |    51 |          51 |
| Cooper  | Wag Tail    |    51 |          51 |
| Max     | Wag Tail    |    87 |          51 |
+---------+-------------+-------+-------------+
9 rows in set (0.00 sec)

Specifying IGNORE NULLS produces an error (at least as of MySQL 8.0.33):

SELECT 
    DogName,
    Activity,
    Score,
    FIRST_VALUE( Score ) IGNORE NULLS OVER win AS "First Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'IGNORE NULLS'

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    DogName,
    Activity,
    Score,
    FIRST_VALUE( Score ) RESPECT NULLS OVER win AS "First Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score DESC
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+-------------+
| DogName | Activity    | Score | First Value |
+---------+-------------+-------+-------------+
| Max     | Fetch Stick |    91 |          91 |
| Cooper  | Fetch Stick |    67 |          91 |
| Bruno   | Fetch Stick |    43 |          91 |
| Max     | Keep Quiet  |    12 |          12 |
| Cooper  | Keep Quiet  |     8 |          12 |
| Bruno   | Keep Quiet  |     1 |          12 |
| Max     | Wag Tail    |    87 |          87 |
| Bruno   | Wag Tail    |    51 |          87 |
| Cooper  | Wag Tail    |    51 |          87 |
+---------+-------------+-------+-------------+
9 rows in set (0.00 sec)

The first value for each partition is now the highest value. That’s because we’ve changed the ordering from ascending to descending order.

Passing a Different Column to `FIRST_VALUE()`

In the above examples, we pass the same column to the FIRST_VALUE() function and the ORDER BY clause. But that’s not a requirement. We can pass different columns if needed.

Here’s an example that uses two different columns for FIRST_VALUE() and the ORDER BY clause:

SELECT 
    District, 
    Name, 
    Population AS "City Population", 
    FIRST_VALUE( Name ) OVER win AS "First Value"
FROM City 
WHERE CountryCode = 'AUS'
WINDOW win AS ( 
    PARTITION BY District 
    ORDER BY Population DESC
    ROWS UNBOUNDED PRECEDING
    );

Result:

+-----------------+---------------+-----------------+-------------+
| District        | Name          | City Population | First Value |
+-----------------+---------------+-----------------+-------------+
| Capital Region  | Canberra      |          322723 | Canberra    |
| New South Wales | Sydney        |         3276207 | Sydney      |
| New South Wales | Newcastle     |          270324 | Sydney      |
| New South Wales | Central Coast |          227657 | Sydney      |
| New South Wales | Wollongong    |          219761 | Sydney      |
| Queensland      | Brisbane      |         1291117 | Brisbane    |
| Queensland      | Gold Coast    |          311932 | Brisbane    |
| Queensland      | Townsville    |          109914 | Brisbane    |
| Queensland      | Cairns        |           92273 | Brisbane    |
| South Australia | Adelaide      |          978100 | Adelaide    |
| Tasmania        | Hobart        |          126118 | Hobart      |
| Victoria        | Melbourne     |         2865329 | Melbourne   |
| Victoria        | Geelong       |          125382 | Melbourne   |
| West Australia  | Perth         |         1096829 | Perth       |
+-----------------+---------------+-----------------+-------------+
14 rows in set (0.01 sec)

Here, we passed the Name column to the FIRST_VALUE() function, but we sorted by population (in descending order). This resulted in the city with the highest population in its partition being presented in the First Value column.

Syntax

Example

Partitioning

The null_treatment Clause

Changing the Order

Passing a Different Column to FIRST_VALUE()

The `null_treatment` Clause

Passing a Different Column to `FIRST_VALUE()`