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

---

]]>