## 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.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 | +---------------------+-----------+---------------------+-----------+ | 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; ```