June 17, 2004 at 8:43 am
Hello,
I'm try to build a query to find missing Ids on a table.
Anyone knows if there's a way of doing that without using a cursor or a
temporary table?
I can find some of the missing IDs using this query:
select distinct t2.TableID
from Table t1 right outer join
(select TableID + 1 as TableID from Table) t2
on (t1.TableID=t1.TableID)
where t2.TableID is null
I could find more IDs by using UNION and changing TableID + 1 with TableID + 2, TableID + 3, etc..
But the TableId can change from 100 to 10000....
Any ideas?
June 17, 2004 at 8:54 am
Itzik Ben-Gan wrote the best series of articles for this in SQL Magazine
http://www.sqlmag.com. They were great.
June 17, 2004 at 9:25 am
Interesting method, it shows the ids and then how many are missing after it. So it doesn't list every one just how many are missing after id 2 and such:
create table table1 (tableid int)
go
insert into table1(tableid)
values(1)
go
insert into table1(tableid)
values(2)
go
insert into table1(tableid)
values(50)
go
insert into table1(tableid)
values(100)
go
SELECT * FROM
(SELECT *, '+' + CONVERT(varchar,(SELECT MIN(TableID) FROM Table1
WHERE tableid > A.tableid) - (tableid + 1)) 'DIFFERENCE' FROM
table1 A WHERE (SELECT MIN(tableid) FROM table1 WHERE tableid >
A.tableid) - tableid >= 2
UNION ALL
SELECT *, '-' + '' 'DIFFERENCE' FROM table1 A
WHERE tableid - (SELECT MAX(tableid) FROM table1 WHERE tableid = 2) AS OUTER_TAB
ORDER BY 1, CASE [DIFFERENCE] WHEN '-' THEN 0 ELSE [DIFFERENCE] END
June 17, 2004 at 10:04 am
Or using my same table above and same concept this just lists the lower missing number and upper missing number for each range missing:
SELECT (tableid + 1) LowerMissing, (SELECT MIN(TableID) FROM Table1
WHERE tableid > A.tableid) -1 UpperMissing FROM
table1 A WHERE (SELECT MIN(tableid) FROM table1 WHERE tableid >
A.tableid) - tableid >= 2
June 17, 2004 at 11:11 am
That's really fine!
It's just what i need, a diferent aproach, i will use a combination of both queries:
SELECT q.LowerMissing, q.UpperMissing, (q.UpperMissing - q.LowerMissing) AS [Difference] FROM
(SELECT (DocVendaid + 1) LowerMissing,
(SELECT MIN(DocVendaID) FROM DocVenda
WHERE DocVendaid > A.DocVendaid) -1 UpperMissing FROM
DocVenda A WHERE (SELECT MIN(DocVendaid) FROM DocVenda WHERE DocVendaid >
A.DocVendaid) - DocVendaid >= 2) q
ORDER BY [Difference] desc
I will use those missing IDs in a sproc for removing duplicated IDs.
Thanks for your help.
Nuno
June 17, 2004 at 1:33 pm
Just standing on the sholders of great minds like Itzik Ben-Gan.
June 17, 2004 at 3:42 pm
A very fast approach is using following:
SELECT dbo.Customers.ID + 1 AS CustomerID
FROM dbo.Customers
WHERE (SELECT MIN(Custs.ID) FROM dbo.Customers Custs WHERE Custs.ID > dbo.Customers.ID) - dbo.Customers.ID > 1
UNION
SELECT dbo.Customers.ID - 1 AS CustomerID
FROM dbo.Customers
WHERE dbo.Customers.ID - (SELECT MAX(Custs.ID) FROM dbo.Customers Custs WHERE Custs.ID < dbo.Customers.ID) > 1
ORDER BY CustomerID
June 17, 2004 at 3:44 pm
Or the more elegant solution:
SELECT dbo.Customers.ID + 1 AS CustomerID, (SELECT MIN(Custs.ID) FROM dbo.Customers Custs WHERE Custs.ID > dbo.Customers.ID) - dbo.Customers.ID - 1 AS Items
FROM dbo.Customers
WHERE (SELECT MIN(Custs.ID) FROM dbo.Customers Custs WHERE Custs.ID > dbo.Customers.ID) - dbo.Customers.ID > 1
ORDER BY dbo.Customers.ID
June 18, 2004 at 2:31 am
Slightly more optimized statement ....
SELECT LowerMissing, UpperMissing , q.UpperMissing - q.LowerMissing + 1 TotalMissing FROM
(
SELECT A.Id + 1 LowerMissing , (SELECT MIN(Id ) FROM MyTable WHERE Id > A.Id ) - 1 UpperMissing
FROM MyTable A
) q
WHERE (q.UpperMissing - q.LowerMissing) >= 0
ORDER BY 1
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply