## 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 `PREPARE`d 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 2017