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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy