Find the next value after a sequence

from the Artful Common Queries page


Given a sequence of values on a given ordering, what is the next value? It's a common requirement (eg in DNA sequencing). A MySQL Forum user posted this example:

drop table if exists data;
create table data(id smallint unsigned primary key auto_increment, val smallint);
insert into data (val) values
(8),(21),(28),(29),(31),(32),(27),(20),(31),(1),(18),(35),
(18),(30),(22),(9),(2),(8),(33),(8),(19),(31),(6),(31),(14),(5),
(26),(29),(34),(34),(19),(27),(29),(3),(21),(18),(31),(5),(18),
(34),(4),(15),(12),(20),(28),(31),(13),(22),(19),(30),(0),(2),
(30),(28),(2),(10),(27),(9),(23),(28),(29),(16),(16),(31),(35),(18),
(2),(15),(1),(30),(15),(11),(17),(26),(35),(1),(22),(19),(23),(1),
(18),(35),(28),(13),(9),(14); 

What value immediately follows the sequence 23,28,29,16,16,31,35?

A simple solution is to form a comma-separated list of values on the given ordering, then locate the target sequence and the next value after it by counting commas:

SET @list = (SELECT Group_Concat(val ORDER BY id) FROM data);
SET @target = '23,28,29,16,16,31,35';
SELECT @pos := Locate( @target, @list, 1 );
SELECT @remainder := SubStr( @list, @pos + Char_length( @target ) + 1 );
SELECT Substr( @remainder, 1, Locate( ',', @remainder ) - 1 ) as NextValue;
+-----------+
| NextValue |
+-----------+
| 18        |
+-----------+

Note that a bug in the MySQL implementation of SET requires that we use SELECT @var := ... syntax after the first two SET assignments.

The calculation seems a natural for a stored function, except that MySQL functions do not support PREPAREd statements. The logic is easily encapsulated in a stored procedure:

drop procedure if exists nextvalue; 
delimiter go 
create procedure nextvalue( tbl char(64), ordcol char(64), valcol char(64) )
begin 
  SET @sql = Concat( 'SELECT Group_Concat(', valcol, ' ORDER BY ', ordcol, ') INTO @list', ' FROM ', tbl );
  PREPARE stmt FROM @sql; EXECUTE stmt;  
  SET @target = '23,28,29,16,16,31,35'; 
  SELECT Locate( @target, @list, 1 ) INTO @pos; 
  SELECT SubStr( @list, @pos + Char_length( @target ) + 1 ) INTO @remainder; 
  DROP PREPARE stmt;
  SELECT Substr( @remainder, 1, Locate( ',', @remainder ) - 1 ); 
end; 
go 
delimiter ; 
call nextvalue('data','id','val'); 


Return to the Artful Common Queries page