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). Other RDBMSs including MariaDB 10.0 have sequence objects ( https://mariadb.com/kb/en/mariadb/sequence-storage-engine/ ). MySQL so far does not, so we have to hand-code the logic. 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');

Last updated 20 Jul 2024