Ndtyjky

I want to identify missing values in an alphanumeric sequence.

The table is defined as such:

CREATE TABLE `seqtest` (

  `ID` int(11) NOT NULL,

  `PoleNo` text,

  `Pre` char(1) DEFAULT NULL,

  `Num` int(3) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data is as shown below and will always be one letter (A-Z) followed by three numbers from 000 to 999.

| PoleNo |  Pre  |  Num  |

|------------------------|

| A000   |   A   |  000  |

| A001   |   A   |  001  |

| A002   |   A   |  002  |

| A004   |   A   |  003  |

| ****   |   *   |  ***  |

| A998   |   A   |  998  |

| A999   |   A   |  999  |

| B000   |   B   |  000  |

| B001   |   B   |  001  |

| B002   |   B   |  002  |

| ****   |   *   |  ***  |

| B998   |   B   |  998  |

| B999   |   B   |  999  |

| C000   |   C   |  000  |

| C001   |   C   |  001  |

| C005   |   C   |  005  |

| C006   |   C   |  006  |

|------------------------|

I want the query to find that, for example, C002, C003 AND C004 are missing as shown below.

|  Pre  | start | stop |

|   C   |   2   |   4  |

|----------------------|

Im using the following:

SELECT l.Pre, l.Num + 1 as start, min(fr.Num) - 1 as stop

FROM seqtest as l

    LEFT OUTER JOIN seqtest as r ON l.Num = r.Num - 1 AND l.Pre = r.Pre

    LEFT OUTER JOIN seqtest as fr ON l.Num < fr.Num AND l.Pre = fr.Pre

WHERE r.Num is null AND l.Num < 999

GROUP BY l.Pre, l.Num, r.Num

which is based on this.

It gives me the range that is missing and works well except for one case...when 'Pre' changes from one letter to the next.

IE With the following data:

| PoleNo |  Pre  |  Num  |

|------------------------|

| B995   |   B   |  995  |

| B996   |   B   |  996  |

| B997   |   B   |  997  |

| C003   |   C   |  003  |

| C004   |   C   |  004  |

| C005   |   C   |  005  |

| C006   |   C   |  006  |

|------------------------| 

Id like to be able to return this:

|  Pre  | start | stop |

|   B   |  998  |  999 |

|   C   |   0   |   2  |

|----------------------|

Is this possible? Im using the Pre and Num fields which are simply the PoleNo field broken up...but if anyone sees a way to do it just using the PoleNo field, that would work as well.

This is much easier in MySQL 8+, because you have lead(). But, you can do what you want as:

select st.pre,

       (st.num + 1) as start,

       (st.next_num - 1) as stop

from (select st.*,

             (select st2.num

              from seqtest st2

              where st2.pre = st.pre and

                    st2.num > st.num

              order by st2.num asc

              limit 1

             ) as next_num

      from seqtest st

     ) st

where next_num <> num + 1;

EDIT:

This gets the ranges at the beginning and end as well:

select st.pre, 

       (st.num + 1) as start,

       (st.next_num - 1) as stop

from (select st.pre, num,

             coalesce( (select st2.num

                        from seqtest st2

                        where st2.pre = st.pre and

                              st2.num > st.num

                        order by st2.num asc

                        limit 1), 1000

                      ) as next_num

      from seqtest st

      union

      select st.pre, 0 as num, min(st.num) as next_num

      from seqtest st

      group by st.pre

     ) st

where next_num <> num + 1

order by pre, start;

Here is a db<>fiddle.