How to get the biggest value in any consecutive range

Question

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

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?

user_10498828 · Answer

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.

user_5176071 · Answer

This might be a gap isolation problem which might need to use the window function to solve and CTE recursive to solve it.

first, we can try to use CTE recursive generator number call startnum range from the smallest number to the biggest of data (from our sample are 1,9), because there is some missing number between them.

Next step we might calculate a grp that is gap-and-island problem feature logic.

The logic might be as below.

continuous(overlapping) data is that a set (continuous range of sequence) - (values based on a certain order of conditions sequence) yields the same grouping.

so we can use

continuous range of sequence: startNum
values based on a certain order: dense_rank window function.

using this logic we might get a grp column as sqlfiddle

Query #1

WITH RECURSIVE cte AS(
   SELECT MIN(val) startNum, MAX(val) endNum
   FROM T
   UNION ALL
   SELECT startNum + 1,endNum
   FROM cte
   WHERE startNum + 1 <= endNum
)
SELECT GROUP_CONCAT(DISTINCT startNum) input,
       MAX(startNum) 'expected output'
FROM (
  SELECT val,
     startNum ,
     startNum - cast(dense_rank() OVER(ORDER BY val)as signed) grp
  FROM cte t1
  LEFT JOIN T t2
  ON t1.startNum = t2.val
) t1
GROUP BY grp
ORDER BY 2;

input	expected output
1,2,3,4	4
5	5
6	6
7	7
8,9	9

View on DB Fiddle

How to get the biggest value in any consecutive range

2 Answers