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

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)

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);

I tried to add this as a comment to Mike's answer, however, it is problematic with the @ signs for variables.

To draw on Mike's answer, the query could actually be made shorter by initializing the variable in the FROM clause, e.g.:

SELECT users_count, @total := @total + users_count AS total
    FROM (messages_queue, (select @total := 0) t)
WHERE @total < 1000;

I think you are looking to do something like this:

SELECT *
FROM
   (SELECT 
        *
      , (select sum(users_count) from `messages_queue` where time <= mq.time) RunningTotal       
   FROM `messages_queue` mq) mq2
WHERE mq2.RunningTotal < 1000

Kudos to Aducci for the pure SQL solution, but as Thomas Berger said, this could end up being a very expensive query. Depending on the size of your table a stored procedure could well be the better approach:

CREATE PROCEDURE messages_to_send
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE oldest_date DATETIME;
  DECLARE cur_count INT;
  DECLARE que_size INT DEFAULT 0;
  DECLARE curs CURSOR FOR SELECT users_count, time FROM messages_que ORDER BY time;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN curs;

  read_loop: LOOP
    FETCH curs INTO cur_count, oldest_date;
    IF done THEN
      LEAVE read_loop;
    END IF;
    que_size = que_size + cur_count
    IF que_size >= 1000
      LEAVE read_loop;
    END IF;
  END LOOP;

  CLOSE curs

  SELECT * FROM messages_que WHERE time < oldest_date;
END

CALL messages_to_send(); --> returns a result set of messages to send with a total user_count of 1000 or less

I don't think you could to this with a simple MySQL Query.

You will have to use a stored procedure or filter that in your application.

EDIT

I'm no MySQL Guru (could only code stored procedures on oracle and postgres) but you could start here: http://www.mysqltutorial.org/sql-cursor-in-stored-procedures.aspx .

More general informations about the syntax is located here: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html