user1576510 user1576510 - 1 year ago 45
SQL Question

Get records whose column sum is as close to preset maximum as possible

I have a table with the following data:

id | duration
1 | 2999
2 | 3219
3 | 3129
4 | 319
5 | 3405
6 | 3084
7 | 3450
8 | 3305
9 | 3485
10 | 3483
11 | 3445
12 | 3570
13 | 1712

I want to write a MYSQL query that will return as many rows as it takes to get the total sum of "duration" to be as close to 12000 as possible. Essentially I want to keep track of the total sum of duration and if a row is going to cause the overall sum of duration then skip it and check the next one. In the data above this would mean returning IDs 1,2,3,4 and 13. I've looked for other posts about this but all the queries they propose return only ids 1,2,3,4 and then stop because id 5 would breach the limit of 12000 - but I need it to keep going and check if there are any records down the line that could still be added.

I know that I could just return all the rows and loop through the results in PHP and keep track of the total duration but I'd rather get this done in the query if possible for efficiency.

Any help is greatly appreciated.

Answer Source

Here's one option using user-defined variables:

select * 
from (
  select id, duration, 
    @overallsum:=@overallsum+duration overall, 
    @prevrunningsum:=@runningsum prevsum,
    @runningsum:=case when @runningsum+duration<12000 then @runningsum+duration
                      else @runningsum
                 end under12000
  from yourtable, (select @overallsum:=0, @runningsum:=0, @prevrunningsum:=0) t
  order by id) t
where prevsum != under12000
order by id