Using MySQL 8.0 Window Functions: first_value, last_value, nth_value, and ntile

The author, a senior database expert with over ten years of MySQL experience, shares practical examples of four MySQL 8.0 window functions that often cause confusion: first_value , last_value , nth_value , and ntile .

Before using these functions, it is essential to understand the window function framework, which defines the partition and ordering of rows within a group. The default frame for last_value is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , which returns the current row's value; to obtain the true last value of a partition, the frame must be set to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING .

first_value : Returns the first row's value in each partition. Example using table t1 (12 rows, partitioned by column r1 ).

localhost:ytt_new>select id,r1,r2 from t1;
+----+------+------+ 
| id | r1   | r2   |
+----+------+------+ 
|  1 | 10   | 20   |
|  2 | 10   | 30   |
|  3 | 10   | 40   |
|  4 | 10   | 50   |
|  5 | 10   | 2    |
|  6 | 10   | 3    |
|  7 | 11   | 100  |
|  8 | 11   | 101  |
|  9 | 11   | 1    |
| 10 | 11   | 3    |
| 11 | 11   | 10   |
| 12 | 11   | 20   |
+----+------+------+
12 rows in set (0.00 sec)

Using first_value simplifies the query:

localhost:ytt_new>select distinct r1,first_value(r2) over(partition by r1 order by r2) as first_r2 from t1;
+------+----------+
| r1   | first_r2 |
+------+----------+
| 10   | 2        |
| 11   | 1        |
+------+----------+
2 rows in set (0.00 sec)

last_value : Returns the last row's value in each partition. The correct frame must be specified to get the actual last value.

localhost:ytt_new>select distinct r1,last_value(r2) over(partition by r1 order by r2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_r2 from t1;
+------+---------+
| r1   | last_r2 |
+------+---------+
| 10   | 50      |
| 11   | 101     |
+------+---------+
2 rows in set (0.00 sec)

nth_value : Returns the value of the nth row in each partition. Example retrieving the first row (equivalent to row_number but with explicit rank input):

localhost:ytt_new>select * from (select distinct r1,nth_value(r2,1) over(partition by r1 order by r2) as first_r2 from t1) T where T.first_r2 is not null;
+------+----------+
| r1   | first_r2 |
+------+----------+
| 10   | 2        |
| 11   | 1        |
+------+----------+
2 rows in set (0.00 sec)

ntile : Divides each partition into a specified number of buckets. The example shows how to select the top 50% of rows using ntile(2) :

localhost:ytt_new>select id,r1,r2 from (select id,r1,r2, ntile(2) over(partition by r1 order by r2) as ntile from t1) T where T.ntile=1;
+----+------+------+ 
| id | r1   | r2   |
+----+------+------+ 
| 5  | 10   | 2    |
| 6  | 10   | 3    |
| 1  | 10   | 20   |
| 9  | 11   | 1    |
|10  | 11   | 3    |
|11  | 11   | 10   |
+----+------+------+ 
6 rows in set (0.00 sec)

In most scenarios, remembering a few common window functions such as row_number() and rank() is sufficient, but understanding the behavior of last_value and the required frame specifications is crucial for correct results.