Find previous and next values in a sequence

from the Artful Common Queries page


Given a table t(ID int, ...), how would we display each ID and its next highest value?

A simple method ...

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