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

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'); 

Return to the Artful Common Queries page