Mysql – SQL get rows where sum of col value is bigger than X

A sample.

Caution! Do not reproduce on large datasets, this may hang your database server!

-- Create a table with the sample data - 10 rows with random values.

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
)
WITH RECURSIVE cte (num) AS (
  SELECT 1 
  UNION ALL 
  SELECT num + 1 FROM cte LIMIT 10
)
SELECT NULL id, 10 + RAND() * 50 val FROM cte;
SELECT * FROM test;

-- The task is to find a combination 
-- where the SUM of `val` values is close but not exceed the limit.

SET @limit = 100;

The solution. In practice we need the closest combination only, so LIMIT 1 should be added to the query. I have not added it, but the rows numbering is added instead. This allows you to understand the amount of investigated combinations and, hence, the size of temporary buffer needed.

WITH RECURSIVE
cte1 (id, val, rn) AS (
  SELECT id, val, ROW_NUMBER() OVER (ORDER BY val DESC, id ASC)
  FROM test
  ),
cte2 (id, val, rn, path, vals, total) AS (
  SELECT id, val, rn,
         CAST(id AS CHAR(65535)),
         CAST(val AS CHAR(65535)),
         val
  FROM cte1
  UNION ALL
  SELECT cte1.id, cte1.val, cte1.rn, 
         CONCAT_WS(',', cte2.path, cte1.id), 
         CONCAT_WS(',', cte2.vals, cte1.val), 
         cte2.total + cte1.val
  FROM cte2
  JOIN cte1 ON cte1.rn > cte2.rn
  WHERE cte2.total + cte1.val <= @limit
  )
SELECT ROW_NUMBER() OVER (ORDER BY total DESC, path ASC) rn,
       path, vals, total
FROM cte2 
ORDER BY total DESC, path ASC /* LIMIT 1 */ ;

fiddle