Mariadb how to reset min and max variable whenever ID sequence breaks

Question

I have a list of ids in between there is some missing id sequence, I wish to generate min and max sequence which I will be using in some other application

MariaDB [test]> select * from book_lists;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | c++ for x |
|  2 | c++ for x |
|  3 | c++ for x |
|  7 | c++ for x |
|  9 | c++ for x |
| 10 | c++ for x |
| 12 | c++ for x |
| 13 | c++ for x |
| 14 | c++ for x |
| 15 | c++ for x |
+----+-----------+
10 rows in set (0.000 sec)

normal min and max produces

MariaDB [test]> select min(id),max(id) from book_lists;
+---------+---------+
| min(id) | max(id) |
+---------+---------+
|       1 |      15 |
+---------+---------+
1 row in set (0.000 sec)

But I would like to reset min and max variable whenever ID sequence breaks, I would like to get below output

+---------+---------+
| min(id) | max(id) |
+---------+---------+
    1       3
    7       7
    9       10
    12      15
+---------+---------+

Below is schema and test data

DROP TABLE IF EXISTS `book_lists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book_lists` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `book_lists`
--

LOCK TABLES `book_lists` WRITE;
/*!40000 ALTER TABLE `book_lists` DISABLE KEYS */;
INSERT INTO `book_lists` VALUES (1,'c++ for x'),(2,'c++ for x'),(3,'c++ for x'),(7,'c++ for x'),(9,'c++ for x'),(10,'c++ for x'),(12,'c++ for x'),(13,'c++ for x'),(14,'c++ for x'),(15,'c++ for x');
/*!40000 ALTER TABLE `book_lists` ENABLE KEYS */;
UNLOCK TABLES;

user_6033601 · Answer

As Akina mentioned already, this is a well-known problem calles "gaps and islands".

Islands are sequential group of rows and gaps are gaps in numeration

To solve it you need to find something that stays the same for the one particular island. In your case we can use (row_number - id) for record numeration. this is what the CTE (with part) does.

The result of "id - row_number()" equation will stay same within an island. Just perform CTE-query alone to get what I mean.

So, after CTE is done, all you you have left to do is to group by islands as the main query does

with gaps_and_islands as (
  select id - row_number() over(order by id) isle, 
         bl.* 
    from book_lists bl)

select min(id), max(id)
  from gaps_and_islands
 group by isle;

So, here is some explanation

The "with"-query usually called as "common table expression" or CTE and is kind of a variable that stores a query.

Without this my query might be rewritten with something like that

select min(id), max(id)
  from (select id - row_number() over(order by id) isle, 
         bl.* 
    from book_lists bl) gaps_and_islands
 group by isle;

Which in my opinion makes it hard to read. So in this case it's being used as a subquery to keep query readable

row_number() over(order by id) - this will sequentially numerate all the rows according to the "over(order by)" part. You can google for "window functions" for your database

For example,

select row_number() over(order by id) isle, 
       bl.id 
    from book_lists bl;

returns:

id	row_number
1	1
2	2
3	3
7	4
9	5
10	6

And this is exactly the point where you problem is solved. The result of substraction of (id - row_number()) will provide us with groups of identical values for every group of sequential ids.

id	row_number	id - row_number
1	1	0
2	2	0
3	3	0
7	4	3
9	5	4
10	6	4
12	7	5
13	8	5
14	9	5
15	10	5

And since you have something to group by, the problem is solved

Mariadb how to reset min and max variable whenever ID sequence breaks

1 Answers