Find row with next value of specified column

from the Artful Common Queries page

We often need next values of a column on a given row ordering. If you are on MySQL 8.0.2 or MariaDB 10.2.2 or later, the analytic functions LAG(...) ... LEAD(...) OVER(...)  provide a straightforward solution.... 
drop table if exists t;
create table t(d timestamp, i int);
insert into t values
('2024-12-1 00:00:00',1),('2024-12-3 00:00:00',3),
('2024-12-5 00:00:00',5),('2024-12-8 00:00:00',8);
SELECT
  LAG(i) OVER (ORDER BY d) Previous,
  d, i,
  LEAD(i) OVER (ORDER BY d) Next
FROM t;
+----------+---------------------+------+------+
| Previous | d                   | i    | Next |
+----------+---------------------+------+------+
|     NULL | 2024-12-01 00:00:00 |    1 |    3 |
|        1 | 2024-12-03 00:00:00 |    3 |    5 |
|        3 | 2024-12-05 00:00:00 |    5 |    8 |
|        5 | 2024-12-08 00:00:00 |    8 | NULL |
+----------+---------------------+------+------+
If you can't use MySQL 8.0.2 or MariaDB 10.2.2 or later, you'll need to carve out the lag-lead logic by hand. We describe a few fairly efficient ways to do it here under the heading "Find previous and next values in a sequence". Here are more ambitious solutions.

The logic is:

1. Form a resultset consisting of all relevant rows, joined with all relevant rows that have greater values in the ordering columns. For example, if the table has these rows: 

+------+
|    2 |
|    4 |
|    6 |
|    8 |
|   10 |
+------+
Then the resultset is 
+------+------+
|    2 |    4 |
|    2 |    6 |
|    2 |    8 |
|    2 |   10 |
|    4 |    6 |
|    4 |    8 |
|    4 |   10 |
|    6 |    8 |
|    6 |   10 |
|    8 |   10 |
+------+------+
2. For each row in this resultset, find the row with the lowest ordering value amongst the higher values. For the example the result of this from the above resultset is 
+-----+-----+
|   2 |   4 |
|   4 |   6 |
|   6 |   8 |
|   8 |  10 |
+-----+-----+
One way to do step #2 is a self-exclusion join (see self-exclusion join examples elsewhere on this page).

Put it together: 

drop table if exists t;
create table t(id int);
insert into t values(2),(4),(6),(8),(10);
select x.aid as id,x.bid as nextvalue
from (
  select a.id as aid,b.id as bid
  from t a
  join t b on a.idy.bid
where y.bid is null
order by x.aid,x.bid;
+------+-----------+
| id   | nextvalue |
+------+-----------+
|    2 |         4 |
|    4 |         6 |
|    6 |         8 |
|    8 |        10 |
+------+-----------+
Modify the algorithm to suit for next lowest. Here is such a solution for the toy table we used at the top ... 
select x.*
from (
  select a.d as thisdate, a.i as thisvalue, b.d as nextdate, b.i as nextvalue
  from t a
  join t b on a.d < b.d
) x
left join (
  select a.d as thisdate, b.d as nextdate
  from t a
  join t b on a.d < b.d
) y on x.thisdate = y.thisdate and x.nextdate > y.nextdate
where y.nextdate is null
order by x.thisdate, x.nextdate; 
+---------------------+-----------+---------------------+-----------+
| thisdate            | thisvalue | nextdate            | nextvalue |
+---------------------+-----------+---------------------+-----------+
| 2024-12-01 00:00:00 |         1 | 2024-12-03 00:00:00 |         3 |
| 2024-12-03 00:00:00 |         3 | 2024-12-05 00:00:00 |         5 |
| 2024-12-05 00:00:00 |         5 | 2024-12-08 00:00:00 |         8 |
+---------------------+-----------+---------------------+-----------+
Scott Noyes helpfully points out that such queries can be simplified by replacing the first exclusion join subquery with a simple inner join: 
select a.d as thisdate, a.i as thisvalue, b.d as nextdate, b.i as nextvalue 
from t a 
join t b on a.d < b.d
left join t c on a.d < c.d and c.d < b.d
where c.d is null 
order by thisdate, nextdate;

Last updated 4 Mar 2025


Return to the Artful Common Queries page