Using SQL to select all possible set of rows using value of a column

Question

I'm wondering if such thing is possible with SQL only. What I'm trying to achieve here is the following:

An SQL table with the following column:

------------
| DURATION |
|----------|
|     5    |
|    14    |
|     3    |
|    25    |
|     .    |
|     .    |
|     .    |

I want to select all possible set of rows satisfying the sum of DURATION from each row being lesser than or greater than a given value. For example if the value is 20 then the result of lesser than 20 should contain 3 sets of rows

14 + 5

5 + 3

14 + 3

If you had a row with 2 in it, would a valid result be 14+3+2? — Nick, Apr 01 '19 at 00:43
Yes, that's the idea. The sum of the values should be less than 20 as I stated. — Amine, Apr 01 '19 at 00:44
MySQL is preferable since it's the one I'm using for my site. But any would work as long as I can implement the idea differently. — Amine, Apr 01 '19 at 00:48

score 3 · Answer 1 · answered Apr 01 '19 at 01:07

Consider a self-join avoiding reverse duplicates with conditions that the two fields are less than zero in sum. NB: This only returns two-pair combinations.

SELECT t1.DURATION, t2.DURATION
FROM myTable t1
LEFT JOIN myTable t2
  ON t1.DURATION < t2.DURATION
WHERE t1.DURATION + t2.DURATION < 20

Nick · Accepted Answer · 2019-04-01T01:33:31.293

Here's a recursive CTE solution (requiring MySQL 8.0+) for finding all combinations of sums of rows that add up to less than a given value. If you don't have MySQL 8, you will probably need to write a stored procedure to do the same looping.

WITH RECURSIVE cte AS (
  SELECT duration,
         duration AS total_duration,
         CAST(duration AS CHAR(100)) AS duration_list 
  FROM test
  WHERE duration < 20
  UNION ALL
  SELECT test.duration,
         test.duration + cte.total_duration, 
         CONCAT(cte.duration_list, ' + ', test.duration)
  FROM test
  JOIN cte ON test.duration > cte.duration AND
              test.duration + cte.total_duration < 20)
SELECT duration_list, total_duration
FROM cte
WHERE duration_list != total_duration
ORDER BY total_duration ASC

Sample output for my demo on dbfiddle:

duration_list   total_duration
2 + 3           5
2 + 5           7
3 + 5           8
2 + 8           10
2 + 3 + 5       10
3 + 8           11
2 + 11          13
5 + 8           13
2 + 3 + 8       13
3 + 11          14
2 + 5 + 8       15
5 + 11          16
2 + 3 + 11      16
3 + 5 + 8       16
2 + 14          16
3 + 14          17
2 + 5 + 11      18
2 + 3 + 5 + 8   18
2 + 3 + 14      19
3 + 5 + 11      19
8 + 11          19
5 + 14          19

This works like a charm, I tested it and it works the way I wanted. Thanks. (Is there any chance this can work with MySQL 5.7? I've tried this with 8.0.15) — Amine, Apr 01 '19 at 02:04
@Amine unfortunately no, it requires recursive CTEs which only came into MySQL with version 8. — Nick, Apr 01 '19 at 02:06
You can check it [here](https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159) — JC Ahn, Apr 01 '19 at 02:09

JC Ahn · Answer 3 · 2019-04-01T01:33:59.207

You can solve the problem with using Common Table Expressions. You should have MySQL 8.0.

See below.

WITH cte (duration) AS (
    SELECT duration 
      FROM your_table
     WHERE duration  < 20
)
SELECT a.duration + b.duration AS 'sum_of_val'
  FROM cte a JOIN cte b 
 WHERE a.duration + b.duration < 20

If you have the other version which dose not support CTE, you can use the Subquery.

See below.

SELECT a.duration + b.duration AS 'sum_of_val'
  FROM (select duration from your_table where duration < 20 ) a
       JOIN (select duration from your_table where duration <20 ) b
 WHERE a.duration + b.duration < 20

Using SQL to select all possible set of rows using value of a column

3 Answers3