Understanding the NTH_VALUE() Function in MySQL

In MySQL, the NTH_VALUE() function is a window function that returns the value of a given expression from the from the N-th row of the window frame, where N is a number that we specify when calling the function.

Syntax

The syntax goes like this:

NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause

Where expr is the expression/column to get the value from. The N argument specifies the row number of the value to return.

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

The optional from_first_last argument is part of the SQL standard, but MySQL allows only FROM FIRST, which is also the default. This causes the calculations to begin at the first row of the window. FROM LAST is parsed, but produces an error.

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,
    NTH_VALUE( Score, 3 ) 
        OVER ( ORDER BY Score ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Max     | Keep Quiet  |     1 |      NULL |
| Bruno   | Keep Quiet  |     2 |      NULL |
| Cooper  | Keep Quiet  |     8 |         8 |
| Bruno   | Fetch Stick |    43 |         8 |
| Cooper  | Wag Tail    |    51 |         8 |
| Bruno   | Wag Tail    |    65 |         8 |
| Cooper  | Fetch Stick |    67 |         8 |
| Max     | Wag Tail    |    87 |         8 |
| Max     | Fetch Stick |    91 |         8 |
+---------+-------------+-------+-----------+

We haven’t applied any partitioning here, and so NTH_VALUE() returns the same value on each row (apart from the NULL values).

Here’s an example of expanding the frame with a frame clause:

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( Score, 3 ) 
        OVER ( 
            ORDER BY Score 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Max     | Keep Quiet  |     1 |         8 |
| Bruno   | Keep Quiet  |     2 |         8 |
| Cooper  | Keep Quiet  |     8 |         8 |
| Bruno   | Fetch Stick |    43 |         8 |
| Cooper  | Wag Tail    |    51 |         8 |
| Bruno   | Wag Tail    |    65 |         8 |
| Cooper  | Fetch Stick |    67 |         8 |
| Max     | Wag Tail    |    87 |         8 |
| Max     | Fetch Stick |    91 |         8 |
+---------+-------------+-------+-----------+

This time there are no NULL values.

Partitioning

This example uses a PARTITION BY clause to partition the results by activity:

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( Score, 3 ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Bruno   | Fetch Stick |    43 |      NULL |
| Cooper  | Fetch Stick |    67 |      NULL |
| Max     | Fetch Stick |    91 |        91 |
| Max     | Keep Quiet  |     1 |      NULL |
| Bruno   | Keep Quiet  |     2 |      NULL |
| Cooper  | Keep Quiet  |     8 |         8 |
| Cooper  | Wag Tail    |    51 |      NULL |
| Bruno   | Wag Tail    |    65 |      NULL |
| Max     | Wag Tail    |    87 |        87 |
+---------+-------------+-------+-----------+

This time it returns a different value each time. That’s because it’s operating within each partition.

The `from_first_last` Argument

As mentioned, the optional from_first_last clause is part of the SQL standard, but MySQL allows only FROM FIRST (which is the default value).

Let’s explicitly specify FROM FIRST:

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( Score, 3 ) FROM FIRST
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Bruno   | Fetch Stick |    43 |      NULL |
| Cooper  | Fetch Stick |    67 |      NULL |
| Max     | Fetch Stick |    91 |        91 |
| Max     | Keep Quiet  |     1 |      NULL |
| Bruno   | Keep Quiet  |     2 |      NULL |
| Cooper  | Keep Quiet  |     8 |         8 |
| Cooper  | Wag Tail    |    51 |      NULL |
| Bruno   | Wag Tail    |    65 |      NULL |
| Max     | Wag Tail    |    87 |        87 |
+---------+-------------+-------+-----------+

Specifying FROM LAST produces an error (at least as of MySQL 8.0.33):

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( Score, 3 ) FROM LAST
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'FROM LAST'

However, we can get the same effect by using ORDER BY to sort in reverse order.

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,
    NTH_VALUE( Score, 3 ) RESPECT NULLS
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Bruno   | Fetch Stick |    43 |      NULL |
| Cooper  | Fetch Stick |    67 |      NULL |
| Max     | Fetch Stick |    91 |        91 |
| Max     | Keep Quiet  |     1 |      NULL |
| Bruno   | Keep Quiet  |     2 |      NULL |
| Cooper  | Keep Quiet  |     8 |         8 |
| Cooper  | Wag Tail    |    51 |      NULL |
| Bruno   | Wag Tail    |    65 |      NULL |
| Max     | Wag Tail    |    87 |        87 |
+---------+-------------+-------+-----------+

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

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( Score, 3 ) IGNORE NULLS
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTH Value"
FROM Dogs;

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,
    NTH_VALUE( Score, 3 )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Max     | Fetch Stick |    91 |      NULL |
| Cooper  | Fetch Stick |    67 |      NULL |
| Bruno   | Fetch Stick |    43 |        43 |
| Cooper  | Keep Quiet  |     8 |      NULL |
| Bruno   | Keep Quiet  |     2 |      NULL |
| Max     | Keep Quiet  |     1 |         1 |
| Max     | Wag Tail    |    87 |      NULL |
| Bruno   | Wag Tail    |    65 |      NULL |
| Cooper  | Wag Tail    |    51 |        51 |
+---------+-------------+-------+-----------+

Passing a Different Column to `NTH_VALUE()`

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

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

SELECT 
    DogName,
    Activity,
    Score,
    NTH_VALUE( DogName, 3 )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score
            ) 
    AS "NTH Value"
FROM Dogs;

Result:

+---------+-------------+-------+-----------+
| DogName | Activity    | Score | NTH Value |
+---------+-------------+-------+-----------+
| Bruno   | Fetch Stick |    43 | NULL      |
| Cooper  | Fetch Stick |    67 | NULL      |
| Max     | Fetch Stick |    91 | Max       |
| Max     | Keep Quiet  |     1 | NULL      |
| Bruno   | Keep Quiet  |     2 | NULL      |
| Cooper  | Keep Quiet  |     8 | Cooper    |
| Cooper  | Wag Tail    |    51 | NULL      |
| Bruno   | Wag Tail    |    65 | NULL      |
| Max     | Wag Tail    |    87 | Max       |
+---------+-------------+-------+-----------+

Syntax

Example

Partitioning

The from_first_last Argument

The null_treatment Clause

Changing the Order

Passing a Different Column to NTH_VALUE()

The `from_first_last` Argument

The `null_treatment` Clause

Passing a Different Column to `NTH_VALUE()`