How to Find "Holes" in a Table

How to find holes in a table

select ID +1 From Table t1
where not exists (select * from Table t2 where t1.id +1 = t2.id);

not sure if this version would be faster than the one you mentioned originally.

SQL Find Holes in Linear Time-Based Table

for SQL Server, try something like this:

SELECT TOP 1 
    DATEADD(second,-10,t1.YourDateColumn)
    FROM YourTable t1
    WHERE NOT EXISTS (SELECT 1
                          FROM YourTable t2 
                          WHERE DATEADD(second,-10,t1.YourDateColumn) = t2.YourDateColumn)
    ORDER BY t1.YourDateColumn DESC

Find holes in mysql table where ids are not unique

I would approach this by getting the next value. Then do some arithmetic on that:

select m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

EDIT:

If you want to do this gaps within m_fon_id, you can just add it in to various parts of the query:

select m_fon_id, m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_id = m.m_fon_id and
                    m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

PostgreSQL Get holes in index column

You can do this by generating a list of all numbers using generate_series() and then check which numbers don't exist in your table.

This can either be done using an outer join:

select nr.i as missing_index
from (
  select i
  from generate_series(1, (select max(rowindex) from examtable1)) i
) nr
  left join examtable1 t1 on nr.i = t1.rowindex
where t1.rowindex is null;

or an not exists query:

select i
from generate_series(1, (select max(rowindex) from examtable1)) i
where not exists (select 1 
                  from examtable1 t1
                  where t1.rowindex = i.i);

I have used a hardcoded lower bound for generate_series() so that you would also detect a missing rowindex that is smaller than the lowest number.

Get minimum unused value in MySQL column

SELECT min(unused) AS unused
FROM (
    SELECT MIN(t1.id)+1 as unused
    FROM yourTable AS t1
    WHERE NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.id = t1.id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM yourTable WHERE id = 1)
) AS subquery

---

Related Topics