How to get the biggest value in any consecutive range

Question

Consider the following values in an indexed column of unsigned integers:

1
2
3
3
2
4
6
8
9

For any number provided, I want to get the biggest value in its consecutive range (continue stepping forward while the next consecutive number exists).

For example, suppose we are provided with the input of 2; the consecutive values of 3 and 4 do exist in the list (regardless of their order), but 5 doesn't exist; therefore our consecutive range would be 2,3,4; thus the expected value would be 4: the biggest value in this consecutive range; having provided with any of 1, 3, or 4, should also yield 4. Thus:

input           expected output
-------------------------------
1,2,3,4                       4  -- any value of the input yields 4
5                             5  -- the input value doesn't even exist in the list
6                             6  -- it's the only value
7                             7  -- the input value doesn't even exist in the list
8,9                           9

So, how to get the biggest value in any consecutive range using MySQL?

Answer 1

Use a recursive CTE:

WITH RECURSIVE cte AS (
  SELECT x FROM tablename WHERE x = ?
  UNION 
  SELECT t.x
  FROM tablename t INNER JOIN cte c
  ON t.x = c.x + 1
)
SELECT COALESCE(MAX(x), ?) x FROM cte;

See the demo.

Or, with DENSE_RANK() window function:

SELECT COALESCE(MAX(CASE WHEN x = rn THEN x END), ?) x
FROM (
  SELECT x, DENSE_RANK() OVER (ORDER BY x) + ? rn
  FROM tablename
  WHERE x > ? AND EXISTS (SELECT * FROM tablename WHERE x = ?)
) t

See the demo.

Replace ? with the input value that you want.