Finding the next available value in MySQL

Question

I have a database table products with the following columns.

ID | segment_key | segment_value
1  |  Mo         | 1
2  |  Mo         | 3
4  |  Jo         | 1
5  |  Jo         | 2
6  |  Ta         | 1

For any given key I need to find the next available segment_value for me to record in the same table.

ie. for the following segment_key list, the expected outputs are

Mo -> 2
Jo -> 3
Ta -> 2
Ji -> 1

I tried the solution mentioned here but I cannot seem to get the right output.

This is my failed attempt.

SELECT t1.segment_value 
FROM products t1
WHERE NOT EXISTS (
    SELECT * 
    FROM products t2
    WHERE t2.segment_value = t1.segment_value + 1 and t2.segment_key='Mo' and t2.is_active=1
)
LIMIT 1

Answer 1

You can try to use CTE RECURSIVE to get the gap of all values. then do CROSS JOIN fill in the gap of value from each segment_key.

Final using OUTER JOIN and filter segment_key IS NULL which represent the gap of values

Query #1

WITH RECURSIVE CTE AS(
  SELECT MIN(segment_value) val,MAX(segment_value) + 1 max_val
  FROM products
  UNION ALL
  SELECT val + 1 ,max_val
  FROM CTE c
  WHERE  val + 1 <= max_val
)  
SELECT c.segment_key,MIN(val) segment_value 
FROM (
  SELECT DISTINCT val,segment_key
  FROM CTE 
  CROSS JOIN products
) c
LEFT JOIN products p
ON c.val = p.segment_value AND c.segment_key = p.segment_key
WHERE p.segment_key IS NULL
GROUP BY c.segment_key;

segment_key	segment_value
Mo	2
Jo	3
Ta	2

---

View on DB Fiddle