How do I find missing data in an array or mySQL table?
I have an array filled with values (twitter-IDs) and I would like to find the missing data between the lowest id and the highest id? Any concern about sharing a simple function or idea on how to do this?
Also, I was wondering if I can do the same with mySQL? I have a key indexed. The table contains 250k rows right now, so a temporary table and then a join won't be very fast or efficient. I could do a PHP loop to iterate over the data, but that would also take a lot of time and a lot of memory. Is there a specific mysql query that I can run? or can I somehow use the function from above?
Thanks James Hartig http://twittertrend.net
0
source to share
3 answers
I had a similar requirement and wrote a function that will return a list of missing identifiers.
---------------------------
create function dbo.FreeIDs ()
---------------------------
returns @tbl table (FreeID int)
as
begin
declare @Max int
declare @i int
select @Max = MAX(ID) from [TheTable]
set @i = 0
while @i < @Max begin
set @i = @i + 1
if not exists (select * from [TheTable] where ID = @i)
insert into @tbl select @i
end
return
end
+1
source to share