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 ( ). 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
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) )
  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; 
  SELECT Substr( @remainder, 1, Locate( ',', @remainder ) - 1 ); 
delimiter ; 
call nextvalue('data','id','val'); 

Last updated 20 Jul 2017