Find previous or next value 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/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 subqueries at all well. Moving retrieval of the next value to a non-equi-join improves performance ... 
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
('2025-01-02', 5),('2025-01-03', 5.5),('2025-01-04', 4),('2025-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 |
+------------+-------------+
| 2025-01-03 |    1.100000 |
| 2025-01-04 |    0.727273 |
| 2025-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.

Last updated 13 Apr 2024