How to "select until" a sum is reached

Question

I'm looking for a way to select until a sum is reached.

My "documents" table has "tag_id" and "size" fields.

I want to select all of the documents with tag_id = 26 but I know I can only handle 600 units of size. So, there's no point in selecting 100 documents and discarding 90 of them when I could have known that the first 10 already added up to > 600 units.

So, the goal is: don't bring back a ton of data to parse through when I'm going to discard most of it.

...but I'd also really like to avoid introducing working with cursors to this app.

I'm using MySQL.

Answer 1

You need some way to order which records get priority over others when adding up to your max units. Otherwise, how do you know which set of records that totals up to 600 do you keep?

SELECT d.id, d.size, d.date_created
FROM documents d
INNER JOIN documents d2 ON d2.tag_id=d.tag_id AND d2.date_created >= d.date_created
WHERE d.tag_id=26
GROUP BY d.id, d.size, d.date_created
HAVING sum(d2.size) <= 600
ORDER BY d.date_created DESC

This is just a basic query to get you started, and there are a number of problems still to solve:

• It stops at <= 600, so in most cases you won't fill up your size limit exactly. This means you might want to tweak it to allow one more record. For example, if the first record is > 600 the query will return nothing, and that could be a problem.
• It won't do anything to check for additional smaller records later on that might still fit under the cap.
• Records with identical date_created values could be sort of 'double counted' here and there.