Sql: Find Missing Ids in a Table

SQL: find missing IDs in a table

This question often comes up, and sadly, the most common (and most portable) answer is to create a temporary table to hold the IDs that should be there, and do a left join. The syntax is pretty similar between MySQL and SQL Server. The only real difference is the temporary tables syntax.

In MySQL:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table IDSeq

In SQL Server:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create table #IDSeq
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    #idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table #IDSeq

SQL Server : efficient way to find missing Ids

JBJ's answer is almost complete. The query needs to return the From and Through for each range of missing values.

select B+1 as [From],A-1 as[Through]from
(select StuffID as A, 
lag(StuffID)over(order by StuffID)as B from Stuff)z
where A<>B+1
order by A

I created a test table with 50 million records, then deleted a few. The first row of the result is:

From   Through
33     35

This indicates that all IDs in the range from 33 through 35 are missing, i.e. 33, 34 and 35.

On my machine the query took 37 seconds.

MySQL get missing IDs from table

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

find Missing ID in table oracle

If I understand correctly, you want the same number of rows for each primary/secondary combination on a serial_id. If that is correct, you can use two levels of aggregation:

select serial_id
from (select serial_id, primary_id, secondary_id, count(*) as cnt
      from t
      group by serial_id, primary_id, secondary_id
     ) ps
group by serial_id
having min(cnt) <> max(cnt);

Find missing numbers in a sequence in MS SQL

Try this:

If you need to get more numbers, just increase the WHERE Number<=100.

DECLARE @Tab1 TABLE (ID INT)

INSERT INTO @Tab1 VALUES(1)
INSERT INTO @Tab1 VALUES(3)
INSERT INTO @Tab1 VALUES(5)
INSERT INTO @Tab1 VALUES(7)
INSERT INTO @Tab1 VALUES(9)

;WITH CTE AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1 FROM CTE
    WHERE Number<=100
)
SELECT TOP 5 *
FROM CTE
WHERE Number NOT IN(SELECT ID FROM @Tab1)
ORDER BY Number
OPTION (maxrecursion 0);

Existing values:

Number
1
3
5
7
9

OutPut:

Number
2
4
6
8
10

Hope this helps you.

How to find missing IDs in MySQL table

Using a Linux script populate a table test(id:auto-increment) up to your max value of your_table. Then execute something like

select id from test where id not in (select id from your_table)

The output will be the missing.

SQL: how do you look for missing ids?

Try with this:

SELECT t1.id FROM your_list t1
LEFT JOIN your_table t2
  ON t1.id = t2.id
WHERE t2.id IS NULL

Add missing rows within a table

The hint would be: Use a join.

One way of approaching this is, that you select the key pairs that you expect and then left join the original table. Be conscious about the missing-value handling, since you have not specified in your question what should happen to those newly created entries.

Test Data

CREATE TABLE test (id INTEGER, doc INTEGER, posi INTEGER, total INTEGER);
INSERT INTO test VALUES (1, 123, 1, 100);
INSERT INTO test VALUES (1, 123, 2, 600);
INSERT INTO test VALUES (1, 123, 3, 200);
INSERT INTO test VALUES (2, 123, 1, 100);
INSERT INTO test VALUES (2, 123, 2, 600);
INSERT INTO test VALUES (2, 123, 3, 200);
INSERT INTO test VALUES (3, 123, 1, 100);
INSERT INTO test VALUES (3, 123, 3, 200);

The possible key combinations can be generated with a cross join:

SELECT DISTINCT a.id, b.posi 
FROM test a, test b

And now join the original table:

WITH expected_lines AS (
    SELECT DISTINCT a.id, b.posi 
    FROM test a, test b
)
SELECT el.id, el.posi, t.doc, t.total
FROM expected_lines el
LEFT JOIN test t ON el.id = t.id AND el.posi = t.posi

You did not describe further, what should happen with the now empty columns. As you may note DOC and TOTAL are null.

My educated guess would be, that you want to make DOC part of the key and assume a TOTAL of 0. If that's the case, you can go with the following:

WITH expected_lines AS (
    SELECT DISTINCT a.id, b.posi, c.doc 
    FROM test a, test b, test c
)
SELECT el.id, el.posi, el.doc, ifnull(t.total, 0) total
FROM expected_lines el
LEFT JOIN test t ON el.id = t.id AND el.posi = t.posi AND el.doc = t.doc

Result

---

Related Topics