January 10, 2006 at 6:38 am
Hello friends..
I have one problem with query...
I have employee table like
create table emp (id int , name varchar(4))
insert into emp values (1,'SS')
insert into emp values (3,'SA')
insert into emp values (4,'SF')
insert into emp values (7,'SG')
insert into emp values (9,'SV')
now i want to know only those ids which are missing here i.e 2,5,6,8 like..i was thinking by creating new column (RowNum int identity (1,1)) but that only gives me id 2 and 5 not 6 and 8 so is there any other query...which required less effort and less load on server if table has lots of such missing ids?????
Regards,
Papillon
January 10, 2006 at 7:49 am
This is easily done with a numbers table. The following query should work, it creates a virtual numbers table and uses it to find missing ids. Just make sure enough numbers are created.
SELECT numbers.n
FROM (
SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000
FROM (
SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS D0 (d)
, (
SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS D1 (d)
, (
SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS D2 (d)
, (
SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS D3 (d)
) AS numbers (n)
WHERE n < (SELECT MAX(id) FROM emp)
AND NOT EXISTS (SELECT * FROM emp WHERE id = numbers.n)
January 10, 2006 at 7:58 am
A quick and dirty way as follows. Not too good when it comes to performance but handy if you do not have permissions to create a numbers table in the database.
====================================================
declare @iMax as int, @iPos as int, @iRes as varchar(5000)
set @iMax = 1000
set @iPos = 1
while @iPos <= @iMax
begin
If exists (select ID from YOURTABLE where ID = @iPos)
Begin
print @iPos-- insert into another table here
End
set @iPos = @iPos + 1
End
================================================
January 10, 2006 at 5:54 pm
Here's one that absolutely rocks! I don't remember the person who wrote it originally, but the two of us played with it and it generates a list on a million rows it about a heartbeat and a half... the neat thing is, no need to generate a huge tally table if you have multi-millions of rows.
--===== This short little ditty is what does the actual work
SELECT MinRange = (SELECT ISNULL(MAX(suba.ID),0)+1
FROM #yourtable suba
WHERE suba.ID < a.ID),
MaxRange = ID - 1
FROM #yourtable a
WHERE a.ID - 1 NOT IN (SELECT ID FROM #yourtable)
AND a.ID - 1 > 0
--Jeff Moden
January 11, 2006 at 2:33 am
You are probably thinking on jratwork in the thread
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=244649
January 11, 2006 at 9:01 pm
Yeaup! That's the one!! You were there, too, Jesper... way too much fun!
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply