Find row with next value of specified column
Sometimes we need next values of a column on a given row ordering. Oracle has a LEAD(...) OVER(...) construct which simplifies this query. MySQL does not. 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 |
+-----+-----+
The fastest 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. 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 |
+---------------------+-----------+---------------------+-----------+