Find row with next value of specified column

from the Artful Common Queries page


Sometimes we need next values of a column on a given row ordering. Oracle has a LEAD(...) OVER(...) construct to simplify this query. MySQL does not.

A few efficient ways to do this are described under "Find previous and next values in a sequence". Here we look at 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 an example where the sequencing column is a timestamp:

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);

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