MariaDB 10.3

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
        name,
        number,
        row_number() OVER (ORDER BY name, number) as rn
    FROM `table`
    WHERE cc = 1
) 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

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