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

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;

CodePudding user response：

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;