MYSQL: Find subsequences in a sequence and group by ID

Question

I have a table with the following result:

------------------
|seq  |   ID     |
------------------
| 1   |  12345   |
| 2   |  12345   |
| 3   |  12345   |
| 4   |  12345   |
| 8   |  12345   |
| 9   |  54321   |
| 10  |  54321   |
| 11  |  54321   |
| 12  |  54321   |
| 13  |  54321   |
| 14  |  12345   |
| 15  |  12345   |
------------------

And right now I’m using the following query:

SELECT min(cardinals.seq) as start, max(cardinals.seq) as stop, r.PART_NUMBER --tao em uso no codigo
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
           ) cardinals
  JOIN (
             SELECT PART_NUMBER, MIN(SERIAL_NUMBER) as minSeq, MAX(SERIAL_NUMBER) as maxSeq
               FROM  manifa6_import_manif
               where wu_name='FT4WU9C7'  
              GROUP BY PART_NUMBER
       ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq
  LEFT JOIN  manifa6_import_manif ON cardinals.seq =  manifa6_import_manif.SERIAL_NUMBER AND r.PART_NUMBER = manifa6_import_manif.part_number
 WHERE  manifa6_import_manif.SERIAL_NUMBER IS NULL

The result of this query is NULL

And I need you to give me the following result:

 -------------------------
 |start  | Stop   | ID   |
 -------------------------
 |  1    |  4     |12345 |
 |  8    |  8     |54321 |
 |  9    |  13    |54321 |
 |  14   |  15    |12345 |
 -------------------------

Can you help me? Thank you

Answer 1

Follows below the work SQL DDL:

---

CREATE DATABASE stackoverflow;

CREATE TABLE `manifa6_import_manif` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `ID` int(11) DEFAULT NULL,
  `PART_NUMBER` varchar(45) DEFAULT NULL,
  `SERIAL_NUMBER` int(11) DEFAULT NULL,
  `wu_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`seq`)
);

---

And as an answer, follow below the SQL DML:

INSERT INTO `manifa6_import_manif` (`seq`,`ID`,`PART_NUMBER`,`SERIAL_NUMBER`,`wu_name`) 
VALUES  (1,12345,'333',1,NULL),
        (2,12345,'333',2,'FT4WU9C7'),
        (3,12345,'333',3,NULL),
        (4,12345,'333',4,NULL),
        (8,54321,'444',8,'FT4WU9C7'),
        (9,54321,'555',9,'FT4WU9C7'),
        (10,54321,'555',10,NULL),
        (11,54321,'555',11,NULL),
        (12,54321,'555',12,NULL),
        (13,54321,'555',13,'FT4WU9C7'),
        (14,12345,'666',14,'FT4WU9C7'),
        (15,12345,'666',15,NULL);


SELECT 
    MIN(mim1.SERIAL_NUMBER) as `start`, 
    MAX(mim1.SERIAL_NUMBER) as `Stop`,
    mim1.ID
FROM  manifa6_import_manif mim1
-- where wu_name='FT4WU9C7'  
GROUP BY mim1.PART_NUMBER;

---

_{NOTE: @Gomez, I had to infer, and I had to popular the`manifa6_import_manif`field. `PART_NUMBER` with data that would enable grouping according to the clause GROUP BY PART_NUMBER. I hope that in the application it will be possible to use the consultation as a solution.}

Guys, for Database questions it would be of great help to put SQL DDL creation of possible work tables.

---

Reference:

[Paul Dubois at al, 2005], Mysql® 5.0: Certification Study Guide