A SQL query to select until SUM(users_count) reaches 1000

Question

I need a sql query to select rows from my messages queue, until SUM(users_count) reaches at most 1000. BUT there is no problem if there be only one row returned and that row's users_count is greater than 1000.

I need something like: (i added my own keywords)

SELECT * FROM `messages_queue` UNTIL SUM(users_count) < 1000 AT LEAST 1 ROW

This is my table structure:

messages_queue
- msg_id
- msg_body
- users_count (number of message recieptors)
- time (insert time)

Answer 1

This solution will perform a cumulative sum, stopping when the sum exceeds 1000:

SELECT NULL AS users_count, NULL AS total
  FROM dual
 WHERE (@total := 0)
 UNION
SELECT users_count, @total := @total + users_count AS total
  FROM messages_queue
 WHERE @total < 1000;

That means that if you have two values of, say, 800, the sum total will be 1600. The first SELECT is just to initialise the @total variable.

If you want to prevent the sum from exceeding 1000, apart from in cases where a single row has a value of greater than 1000, then I think this works, although you'll need to subject it to some rigorous testing:

SELECT NULL AS users_count, NULL AS total, NULL AS found
  FROM dual
 WHERE (@total := 0 OR @found := 0)
 UNION
SELECT users_count, @total AS total, @found := 1 AS found
  FROM messages_queue
 WHERE (@total := @total + users_count)
   AND @total < 1000
 UNION
SELECT users_count, users_count AS total, 0 AS found
  FROM messages_queue
 WHERE IF(@found = 0, @found := 1, 0);