SQL Server Performance

help pleas

Discussion in 'T-SQL Performance Tuning for Developers' started by erajendar, Aug 10, 2003.

  1. erajendar New Member

    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
  2. Syd New Member

    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
  3. rushmada New Member

    Hi Rajendar,

    U can use distinct clause in select to eliminate duplicates.

    Select distinct Numbers from tabnum


    Rushendra
  4. Syd New Member

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

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

    Yah!!!!!! I've Table called Numbers with only field called No.

    Duplicates should be deleted from the table using sql queries

    Rajendar

    ok
  7. Syd New Member

    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
    -----------------------------------
  8. Syd New Member

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

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

    Rajendar


    ok
  10. bambola New Member

    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.
  11. erajendar New Member

    Thankx alot bambola.

    Rajendar

    ok

Share This Page