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');
drop temporary table if exists tmp_tbl;
create temporary table tmp_tbl (
rn int unsigned auto_increment primary key,
name varchar(64) not null,
number int not null
);
insert into tmp_tbl (name, number)
select name, number
from `table`
order by name, number;
SELECT
min(number) first_number,
max(number) last_number,
count(*) AS no_records,
name
FROM tmp_tbl
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 |