SQL Server Performance Forum – Threads Archive

# help pleas

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.

Thankx alot bambola. Rajendar ok

]]>