BACKGROUND: We have a table we use to queue actions that need to be completed in order by id, but we need to grab small groups to process without breaking up the sequential userids.
Using the data set below where we want to select roughly 10 rows (in reality it's much more than this) that have not been completed.
THE QUESTION: How to select a dynamic number of rows in MySQL without breaking sequential userids?
BTW, I have reviewed the following questions that don't really illuminate how to accomplish my specific task.
- How to count the consecutive duplicate rows in mysql
- Arrange duplicates and number the records in a sequence - MySQL
- https://dba.stackexchange.com/questions/188775/selecting-consecutive-rows-with-same-values
- Is storing a delimited list in a database column really that bad? (Dear Moderator: this is not applicable, and my question is not duplicate. My question has nothing to do with delimited lists. Our data is not delimited.)
Notice in the faulty example query below that it cuts off 2 rows for userid 1083. We set a limit to 10, yes. So the query did what it should. However, how do we 'pick up' the next 2 for userid 1083, or leave off the last 2 userid 1083 which would then be selected as part of the next select of uncompleted actions?
EXAMPLE QUERY (faulty):
SELECT id, userid, action_id, specific_id, index_id, internal_id, extra_data, completed
FROM test_table
WHERE completed = 0
ORDER BY id LIMIT 10
EXAMPLE RESULT (faulty):
id userid action_id specific_id index_id internal_id extra_data completed
------ ------ --------- ----------- -------- ----------- ---------- -----------
5 1081 22 1081 4 2 12 0
6 1081 22 1081 4 2 11 0
7 1081 41 1081 21 2 1,6,1,5 0
8 1081 41 1081 21 2 2,6,2,1 0
9 1082 22 1082 4 2 12 0
10 1082 22 1082 4 2 11 0
11 1082 41 1082 21 2 1,6,1,5 0
12 1082 41 1082 21 2 2,6,2,1 0
13 1083 22 1083 4 2 12 0
14 1083 22 1083 4 2 11 0
ACCEPTABLE RESULTS
ACCEPTABLE EXAMPLE 1:
id userid action_id specific_id index_id internal_id extra_data completed
------ ------ --------- ----------- -------- ----------- ---------- -----------
5 1081 22 1081 4 2 12 0
6 1081 22 1081 4 2 11 0
7 1081 41 1081 21 2 1,6,1,5 0
8 1081 41 1081 21 2 2,6,2,1 0
9 1082 22 1082 4 2 12 0
10 1082 22 1082 4 2 11 0
11 1082 41 1082 21 2 1,6,1,5 0
12 1082 41 1082 21 2 2,6,2,1 0
13 1083 22 1083 4 2 12 0
14 1083 22 1083 4 2 11 0
15 1083 41 1083 21 2 1,6,1,5 0
16 1083 41 1083 21 2 2,6,2,1 0
OR
ACCEPTABLE EXAMPLE 2:
id userid action_id specific_id index_id internal_id extra_data completed
------ ------ --------- ----------- -------- ----------- ---------- -----------
5 1081 22 1081 4 2 12 0
6 1081 22 1081 4 2 11 0
7 1081 41 1081 21 2 1,6,1,5 0
8 1081 41 1081 21 2 2,6,2,1 0
9 1082 22 1082 4 2 12 0
10 1082 22 1082 4 2 11 0
11 1082 41 1082 21 2 1,6,1,5 0
12 1082 41 1082 21 2 2,6,2,1 0
TEST DATA SET
id userid action_id specific_id index_id internal_id extra_data completed
------ ------ --------- ----------- -------- ----------- ---------- -----------
1 1080 22 1080 4 2 12 1
2 1080 22 1080 4 2 11 1
3 1080 41 1080 21 2 1,6,1,5 1
4 1080 41 1080 21 2 2,6,2,1 1
5 1081 22 1081 4 2 12 0
6 1081 22 1081 4 2 11 0
7 1081 41 1081 21 2 1,6,1,5 0
8 1081 41 1081 21 2 2,6,2,1 0
9 1082 22 1082 4 2 12 0
10 1082 22 1082 4 2 11 0
11 1082 41 1082 21 2 1,6,1,5 0
12 1082 41 1082 21 2 2,6,2,1 0
13 1083 22 1083 4 2 12 0
14 1083 22 1083 4 2 11 0
15 1083 41 1083 21 2 1,6,1,5 0
16 1083 41 1083 21 2 2,6,2,1 0
17 394 49 1 2 99 0
18 394 40 1 3 9 0
19 394 18 1 3 9 1 0
20 3 49 2 2 99 0
21 394 49 3 2 99 0
22 394 49 4 2 99 0
23 3 40 3 3 9 0
24 3 18 3 3 9 1 0
25 3 51 3 3 9 0
26 3 22 2 5 20 9 0
Use this to build the table:
CREATE TABLE `test_table` (
`id` INT (11),
`userid` INT (11),
`action_id` INT (11),
`specific_id` VARCHAR (250),
`index_id` INT (11),
`internal_id` INT (11),
`extra_data` VARCHAR (250),
`completed` INT (11)
);
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('1','1080','22','1080','4','2','12','1');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('2','1080','22','1080','4','2','11','1');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('3','1080','41','1080','21','2','1,6,1,5','1');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('4','1080','41','1080','21','2','2,6,2,1','1');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('5','1081','22','1081','4','2','12','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('6','1081','22','1081','4','2','11','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('7','1081','41','1081','21','2','1,6,1,5','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('8','1081','41','1081','21','2','2,6,2,1','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('9','1082','22','1082','4','2','12','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('10','1082','22','1082','4','2','11','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('11','1082','41','1082','21','2','1,6,1,5','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('12','1082','41','1082','21','2','2,6,2,1','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('13','1083','22','1083','4','2','12','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('14','1083','22','1083','4','2','11','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('15','1083','41','1083','21','2','1,6,1,5','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('16','1083','41','1083','21','2','2,6,2,1','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('17','394','49','1','2','99','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('18','394','40','1','3','9','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('19','394','18','1','3','9','1','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('20','3','49','2','2','99','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('21','394','49','3','2','99','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('22','394','49','4','2','99','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('23','3','40','3','3','9','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('24','3','18','3','3','9','1','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('25','3','51','3','3','9','','0');
INSERT INTO `test_table` (`id`, `userid`, `action_id`, `specific_id`, `index_id`, `internal_id`, `extra_data`, `completed`) VALUES('26','3','22','2','5','20','9','0');
