Find previous and next values in a sequence

from the Artful Common Queries page


Given a table t(ID int, ...), how to display each ID and its previous and next highest values? If we are on MySQL 8.0.2 or MariaDB 10.2.2 or later, the new Lag() and Lead() analytic functions do the job, no fuss:

SELECT 
  LAG(i) OVER () Previous, 
  i, 
  LEAD(i) OVER () Next 
FROM t;

If we are on earlier versions, here is a simple method for the one-column case ...

SELECT id, (SELECT MIN(id) from t as x WHERE x.id > t.id) AS Next
FROM t
ORDER BY id;

...but early versions of MySQL did not optimise correlated qubqueries at all well.

Moving retrieval of the next value to a non-equi-join improves optimisation ...

SELECT id, MIN(b.id) AS Next 
FROM t AS a 
JOIN t AS b ON b.id > a.id
GROUP BY  a.id;

The same logic can retrieve next lowest values.

Suppose we wish to track daily changes in closing price:

drop table if exists t;
create table t (date date, closing_price decimal(8,2) );
insert into t values
('2009-01-02', 5),('2009-01-03', 5.5),('2009-01-04', 4),('2009-01-05', 6);

We can use the above non-equi-self-join to compute the ratios:

SELECT 
  seq.Next AS date, t.closing_price/seq.closing_price AS ChangeRatio
FROM t 
JOIN (
  SELECT a.date, a.closing_price, MIN(b.date) AS Next 
  FROM t AS a 
  JOIN t AS b ON b.date > a.date
  GROUP BY  a.date
) seq ON t.date=seq.next;
+------------+-------------+
| date       | ChangeRatio |
+------------+-------------+
| 2009-01-03 |    1.100000 |
| 2009-01-04 |    0.727273 |
| 2009-01-05 |    1.500000 |
+------------+-------------+

Here is another algorithm, by Baron Schwartz (xaprb.com), for retrieving the previous and next column values in a sequence, given a particular column value thisvalue. The previous value is the maximum value less than thisvalue, and the next value is the minimum value greater than thisvalue:

SELECT 
  IF(col > thisvalue,'next','prev') AS Direction,
  IF(col > thisvalue,MIN(col),MAX(col)) AS 'Prev/Next' 
FROM tablename 
WHERE col <> thisvalue
GROUP BY SIGN(col - thisvalue);

So, to find the previous and next order ids in the Northwind database table orders (nwib.orders), starting from order number 10800:

SELECT 
  IF(orderid > 10800,'next','prev') AS Direction,
  IF(orderid > 10800,MIN(orderid),MAX(orderid)) AS 'Prev/Next' 
FROM nwib.orders 
WHERE orderid <> 10800
GROUP BY SIGN(orderid - 10800);
+-----------+-----------+
| Direction | Prev/Next |
+-----------+-----------+
| prev      | 10799     |
| next      | 10801     |
+-----------+-----------+

This is a natural for a stored procedure:

DROP PROCEDURE IF EXISTS PrevNext;
DELIMITER |
CREATE PROCEDURE PrevNext( 
  IN db CHAR(64), IN tbl CHAR(64), IN col CHAR(64), IN seq INT 
)
BEGIN
  IF db IS NULL OR db = '' THEN
    SET db = SCHEMA();
  END IF;
  SET @sql = CONCAT( "SELECT ", 
                     " IF(", col, " > ", seq,",'next','prev') AS Direction,",
                     " IF(", col, " > ", seq, ",MIN(", col, "),MAX(", col, ")) AS 'Prev/Next'",
                     " FROM ", db, ".", tbl, 
                     " WHERE ", col, " <> ", seq,
                     " GROUP BY SIGN(", col, " - ", seq, ")" );
  PREPARE stmt FROM @sql;
  EXECUTE  stmt;
  DEALLOCATE PREPARE stmt;
END;
|
DELIMITER ;

Or, it can be embedded in the FROM clause of another query, for example ...

SELECT o2.OrderID,o2.Value,o.customerid
FROM orders o
JOIN (
  SELECT 'This' AS 'OrderId', 10800 AS 'Value'
  UNION
  SELECT 
    IF( orderid > 10800, 'Next', 'Prev') AS Which,
    IF( orderid > 10800, MIN(orderid), MAX(orderid )) AS 'Value' 
  FROM orders 
  WHERE orderid <> 10800
  GROUP BY SIGN( orderid - 10800 )
) AS o2 ON o.orderid=o2.value 
ORDER BY o.orderid;
+---------+-------+------------+
| OrderID | Value | customerid |
+---------+-------+------------+
| Prev    | 10799 | KOENE      |
| This    | 10800 | SEVES      |
| Next    | 10801 | BOLID      |
+---------+-------+------------+

It gets more complicated if there can be duplicate values of col. Assuming a primary key ID=thisID and the ordering column col='thisvalue', Rick James finds this solution for the previous col value...

SELECT  ...
FROM  ...
WHERE col<='thisvalue' AND ( col<'thisvalue' OR  id<thisID )     
ORDER by col DESC, id DESC LIMIT 1

Invert the comparison logic for the next value.

Return to the Artful Common Queries page