By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.3.23-MariaDB |
CREATE TABLE `table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cc` int(3) unsigned NOT NULL,
`number` int(10) NOT NULL,
`name` varchar(64) NOT NULL,
`datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `table` (`id`, `cc`, `number`, `name`) VALUES
(1, 1, 12, 'Hello'),
(2, 1, 2, 'Apple'),
(3, 1, 3, 'Bean'),
(4, 1, 10, 'Hello'),
(5, 1, 11, 'Hello'),
(6, 1, 1, 'Apple'),
(7, 1, 14, 'Deer'),
(8, 1, 14, 'Door'),
(9, 1, 15, 'Hello'),
(10, 1, 17, 'Hello');
SELECT
min(number) first_number,
max(number) last_number,
count(*) AS no_records,
name
FROM (
SELECT c.*, @rn := @rn + 1 rn
from (
SELECT name, number
FROM `table`
WHERE cc = 1
ORDER BY name, number
LIMIT 99999999999999999
) AS c
CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY name, number - rn
ORDER BY first_number ASC, name ASC;
| first_number | last_number | no_records | name |
|---|---|---|---|
| 1 | 2 | 2 | Apple |
| 3 | 3 | 1 | Bean |
| 10 | 12 | 3 | Hello |
| 14 | 14 | 1 | Deer |
| 14 | 14 | 1 | Door |
| 15 | 15 | 1 | Hello |
| 17 | 17 | 1 | Hello |