Find previous and next values in a sequence
Given a table t(ID int, ...), how would we display each ID and its next highest value? A simple method uses aggregation on a non-equi-self-join to find the smallest value of all greater values for each value:
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 |
+------------+-------------+
| 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 |
+---------+-------+------------+