Hai folks, This is the Question, being asked by my professor. I've a Table called Numbers which stores numbers like below: The Table strictly contains only one field Numbers. Numbers --------- 1 1 1 2 3 2 4 3 3 5 5 5 Now the question is I should eliminate the duplicates using the query(without uisng any temporary tables) Does anyone have answers.. Rajendar ok

declare @max int, @iter int, @value int select @max = max( numbers ) from numbers select @iter = 1 while @iter <= @max begin select @value = numbers from numbers where numbers = @iter delete from numbers where numbers = @value insert into numbers values ( @value ) select @iter = @iter + 1 end

Hi Rajendar, U can use distinct clause in select to eliminate duplicates. Select distinct Numbers from tabnum Rushendra

rushmada<br />i bolive eliminate duplicates means 'delete duplicates from table' <img src='/community/emoticons/emotion-1.gif' alt='' />

becides what's "tabnum" ?<br />dude said "I've a Table called Numbers" <img src='/community/emoticons/emotion-1.gif' alt='' />))

Yah!!!!!! I've Table called Numbers with only field called No. Duplicates should be deleted from the table using sql queries Rajendar ok

erajendar but in the first post you wrote: "The Table strictly contains only one field Numbers" now you say "only field called No" my code then has changed: ------------------------------------ declare @max int, @iter int, @value int select @max = max( no ) from numbers select @iter = 1 while @iter <= @max begin select @value = no from numbers where no = @iter delete from numbers where no = @value insert into numbers values ( @value ) select @iter = @iter + 1 end -----------------------------------

Rajendar Select distinct No from Numbers won't help u coz it returns only a data set but doesn't change anything

Thankx syed. My query was same in two cases. Only to clarify other chaps doubts, I've rewritten the requirement. Rajendar ok

Here's another way declare @rc int, @value int, @count int select @count = count([no]), @value = [no] from numbers group by [no] having count([no]) > 1 order by [no] while @count > 1 begin set @rc = @count - 1 set rowcount @rc delete from numbers where [no] = @value set rowcount 0 select @count = 0 select top 1 @value = [no], @count = count([no]) from numbers group by [no] having count([no]) > 1 order by [no] end select [no] from numbers Bambola.