phpmyadmin Question posted in
The official documentation can be found here.

mysql : check if column id is Consecutive – Phpmyadmin

I have a table that contains IDs.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  9 |
|  7 |
| 10 |

Number 5 and number 8 are not in the table.
I want to select only those rows from the table

Tags:

3

Answers


  1. 0

    If you want the missing ids, one option is a recursive query to generate the number list, and then not exists (this requires MySQL 8.0):

    with cte as (
    select min(id) id, max(id) max_id from mytable
    union all 
    select id + 1, max_id from cte where id < max_id
)
select c.id
from cte c
where not exists (select 1 from mytable t where t.id = c.id)
    Login or Signup to reply.
  2. 0

    You can get the gaps by looking at the previous row. If your table is not too large:

    select (t.prev_id + 1) as first_missing,
       (t.id - 1) as last_missing,
       (t.id - t.prev_id - 1) as cnt
from (select t.*,
             (select max(t2.id) 
              from t t2
              where t2.id < t.id
             ) as prev_id
      from t
     ) t
where t.prev_id <> t.id - 1;

    Actually splitting this out into separate rows is tricky in earlier versions of MySQL unless you have a number or tally table.

    If your data is large, you can use variables instead:

    select (t.prev_id + 1) as first_missing,
       (t.id - 1) as last_missing,
       (t.id - t.prev_id - 1) as cnt
from (select t.*,
             (case when (@temp := @prev) = null
                   then null  -- never happens
                   when (@prev := id) = null       
                   then null  -- never happens
                   else @temp
               end) as prev_id
      from (select t.* from t order by id) t cross join
           (select @prev := -1) params
     ) t
where t.prev_id <> t.id - 1;
    Login or Signup to reply.
  3. 0 
    SELECT MIN(id) - 1 id
FROM ( SELECT id, id - @group_number:=@group_number + 1 group_number
       FROM test, (SELECT @group_number := 0) init_variable
       ORDER BY id ) subquery
GROUP BY group_number
HAVING id;

    fiddle

    If there is a lot of consecutive lost numbers only the last one will be returned.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search