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(...) make this straightforward. Given this toy table ...

drop table if exists t;
create table t(d timestamp, i int);
insert into t values
('2009-12-1 00:00:00',1),('2009-12-3 00:00:00',3),
('2009-12-5 00:00:00',5),('2009-12-8 00:00:00',8);


...then LAG(...)...LEAD(...) OVER(...) give this solution immediately...

SELECT
  LAG(i) OVER (ORDER BY d) Previous,
  d, i,
  LEAD(i) OVER (ORDER BY d) Next
FROM t;
+----------+---------------------+------+------+
| Previous | d                   | i    | Next |
+----------+---------------------+------+------+
|     NULL | 2009-12-01 00:00:00 |    1 |    3 |
|        1 | 2009-12-03 00:00:00 |    3 |    5 |
|        3 | 2009-12-05 00:00:00 |    5 |    8 |
|        5 | 2009-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've desscribed a few fairly efficient ways to do this under "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.id<b.id
) x
left join (
  select a.id as aid,b.id as bid
  from t a
  join t b on a.id<b.id
) y on x.aid=y.aid and x.bid>y.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 |
+---------------------+-----------+---------------------+-----------+
| 2009-12-01 00:00:00 |         1 | 2009-12-03 00:00:00 |         3 |
| 2009-12-03 00:00:00 |         3 | 2009-12-05 00:00:00 |         5 |
| 2009-12-05 00:00:00 |         5 | 2009-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;


Return to the Artful Common Queries page