MySQL Select Query when wanting groups of x elements returned in batches

Question

How to return a select result with batches through a single query, that should be a straight forward compound select somehow. Below is a sample table and a simple query that will generate the desired result by currently using a temporary numeric column that could not work in the final real world process.

There are only two key columns involved: ip addresses and oid addresses for various SNMP items at that address. Need to have the returned results in groups of up to 10 items per ip address and then going to the next IP address and return up to 10 more and so on and when one pass through all the IP addresses is complete go back to the first IP and return the second group of up to 10, next IP and 10 and so on.

Here is some sample data and simple query that somehow needs to be a compound query

-- Table structure for table test

CREATE TABLE `test` (
 `ip` varchar(16) collate latin1_general_ci NOT NULL,
 `oid` varchar(50) collate latin1_general_ci NOT NULL,
 `element` varchar(16) collate latin1_general_ci NOT NULL,
 `temp` tinyint(4) NOT NULL,
 PRIMARY KEY  (`ip`,`oid`),
 KEY `element` (`element`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table test

INSERT INTO `test` VALUES 
   ('1', '1.1.1', 'a', 1),
   ('1', '1.1.2', 'b', 1),
   ('1', '1.1.3', 'c', 1),
   ('1', '1.1.4', 'd', 1),
   ('1', '1.1.5', 'e', 1),
   ('1', '1.1.6', 'f', 1),
   ('1', '1.1.7', 'g', 1),
   ('1', '1.1.8', 'h', 1),
   ('1', '1.1.9', 'i', 1),
   ('1', '1.1.10', 'j', 1),
   ('1', '1.1.11', 'k', 5),
   ('1', '1.1.12', 'l', 5),
   ('1', '1.1.13', 'm', 5),
   ('1', '1.1.14', 'n', 5),
   ('1', '1.1.15', 'o', 5),
   ('1', '1.1.16', 'p', 5),
   ('1', '1.1.17', 'q', 5),
   ('1', '1.1.18', 'r', 5),
   ('1', '1.1.19', 's', 5),
   ('1', '1.1.20', 't', 5),
   ('1', '1.1.21', 'u', 9),
   ('1', '1.1.22', 'v', 9),
   ('1', '1.1.23', 'w', 9),
   ('1', '1.1.24', 'x', 9),
   ('1', '1.1.25', 'y', 9),
   ('1', '1.1.26', 'z', 9),
   ('2', '1.1.1', 'a', 2),
   ('2', '1.1.2', 'b', 2),
   ('2', '1.1.3', 'c', 2),
   ('2', '1.1.4', 'd', 2),
   ('2', '1.1.5', 'e', 2),
   ('2', '1.1.6', 'f', 2),
   ('2', '1.1.7', 'g', 2),
   ('2', '1.1.8', 'h', 2),
   ('2', '1.1.9', 'i', 2),
   ('2', '1.1.10', 'j', 2),
   ('2', '1.1.11', 'k', 6),
   ('2', '1.1.12', 'l', 6),
   ('2', '1.1.13', 'm', 6),
   ('2', '1.1.14', 'n', 6),
   ('2', '1.1.15', 'o', 6),
   ('2', '1.1.16', 'p', 6),
   ('2', '1.1.17', 'q', 6),
   ('2', '1.1.18', 'r', 6),
   ('2', '1.1.19', 's', 6),
   ('2', '1.1.20', 't', 6),
   ('2', '1.1.21', 'u', 10),
   ('2', '1.1.22', 'v', 10),
('2', '1.1.23', 'w', 10),
('2', '1.1.24', 'x', 10),
('2', '1.1.25', 'y', 10),
('2', '1.1.26', 'z', 10),
('3', '1.2.1', 'a', 3),
('3', '1.2.2', 'b', 3),
('3', '1.2.3', 'c', 3),
('3', '1.2.4', 'd', 3),
('3', '1.2.5', 'e', 3),
('3', '1.2.6', 'f', 3),
('3', '1.2.7', 'g', 3),
('3', '1.2.8', 'h', 3),
('3', '1.2.9', 'i', 3),
('3', '1.2.10', 'j', 3),
('3', '1.2.11', 'k', 7),
('3', '1.2.12', 'l', 7),
('3', '1.2.13', 'm', 7),
('3', '1.2.14', 'n', 7),
('3', '1.2.15', 'o', 7),
('3', '1.2.16', 'p', 7),
('3', '1.2.17', 'q', 7),
('3', '1.2.18', 'r', 7),
('3', '1.2.19', 's', 7),
('3', '1.2.20', 't', 7),
('3', '1.2.21', 'u', 11),
('3', '1.2.22', 'v', 11),
('3', '1.2.23', 'w', 11),
('3', '1.2.24', 'x', 11),
('3', '1.2.25', 'y', 11),
('3', '1.2.26', 'z', 11),
('4', '1.2.1', 'a', 4),
('4', '1.2.2', 'b', 4),
('4', '1.2.3', 'c', 4),
('4', '1.2.4', 'd', 4),
('4', '1.2.5', 'e', 4),
('4', '1.2.6', 'f', 4),
('4', '1.2.7', 'g', 4),
('4', '1.2.8', 'h', 4),
('4', '1.2.9', 'i', 4),
('4', '1.2.10', 'j', 4),
('4', '1.2.11', 'k', 8),
('4', '1.2.12', 'l', 8),
('4', '1.2.13', 'm', 8),
('4', '1.2.14', 'n', 8),
('4', '1.2.15', 'o', 8),
('4', '1.2.16', 'p', 8),
('4', '1.2.17', 'q', 8),
('4', '1.2.18', 'r', 8),
('4', '1.2.19', 's', 8),
('4', '1.2.20', 't', 8),
('4', '1.2.21', 'u', 12),
('4', '1.2.22', 'v', 12),
('4', '1.2.23', 'w', 12),
('4', '1.2.24', 'x', 12),
('4', '1.2.25', 'y', 12),
('4', '1.2.26', 'z', 12);

Query:

  SELECT `ip` , `oid` , `element`
    FROM `test`
ORDER BY `temp` ASC , `ip` ASC , `oid` ASC
   LIMIT 999

The following select query returns the desired result now just need to figure out how to remove the temp column and create a select query that generates the same or similar result.

Any help would be appreciated

Answer 1

Need to have the returned results in groups of up to 10 items per ip address and then going to the next IP address and return up to 10 more and so on and when one pass through all the IP addresses is complete go back to the first IP and return the second group of up to 10, next IP and 10 and so on.

MySQL unfortunately does not support the ROW_NUMBER() function that most other brands of database support, but you can simulate it with a user variable.

The following is tested with your data and MySQL 5.1.49:

SET @rownum := 0;
SET @ip := null;

SELECT * FROM (
    SELECT IF(@ip=ip,@rownum:=@rownum+1,@rownum:=0) AS rownum, @ip:=ip AS ip, oid
    FROM test ORDER BY ip, oid
) AS t
ORDER BY FLOOR(rownum/10), ip, oid;