Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2012
    Posts
    15

    Question Unanswered: eliminate duplicate rows

    Anyone help on this query? how to eliminate the duplicate rows for the below query..

    1>select * from dummytable
    2>go
    sno item
    -------------- ------
    1 A
    1 A
    2 B
    2 B
    3 C
    3 D
    4 A
    5 B

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    Try this :

    1>select distinct * from dummytable
    2>go
    sno item
    -------------- ------
    1 A
    2 B
    3 C
    3 D
    4 A
    5 B



    This removes the duplicate rows ( both columns have to be equal ).


  3. #3
    Join Date
    Jun 2012
    Posts
    15
    i want to delete the duplicate rows completely from the dummy table... and the output should be as above.... query..... without using temporary tables in tempdb.
    Last edited by kumz; 07-18-12 at 08:14.

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hey so what you want is to delete the rows from the table not query.

    Here you go, this is an example that i have from a previous situation like that,
    adapt to your situation:


    create table Example(
    id numeric(5,0) identity,
    column1 varchar(10),
    column2 varchar(10)
    )


    insert Example(column1,column2) values("AAA","AAA")
    insert Example(column1,column2) values("AAA","AAA")
    insert Example(column1,column2) values("AAA","AAA")
    insert Example(column1,column2) values("AAA","AAA")
    insert Example(column1,column2) values("AAA","BBB")
    insert Example(column1,column2) values("BBB","BBB")
    insert Example(column1,column2) values("BBB","AAA")

    select * from Example


    begin tran
    delete Example where id not in (select max( id )
    from Example group by column1,column2)
    go
    select * from Example
    go
    commit


    drop table Example


    Hope it helps.

  5. #5
    Join Date
    Jun 2012
    Posts
    15
    Thanks for the reply ! Is it possible to delete the duplicates without identity constraint ?

  6. #6
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    You're welcome!
    That's what we are all for.

    I would say no, because I don't believe that's possible to have an identity column with duplicate values.

    Do you have duplicate rows with identity?

  7. #7
    Join Date
    Jun 2012
    Posts
    15
    I just want to know is it possible to remove duplication, if that table (Example table above mentioned) does'nt have the identity column (id). Say that table contain only column1 and column2.

    column1 column2
    ---------- ---------
    AA A
    AA A
    BC B
    BC B
    DD C
    EE D
    Here "AA A" and "BC B" row is duplicated .
    Is it possible to remove that duplication without using temp tables.
    Say output should be..

    column1 column2
    ---------- ---------
    AA A
    BC B
    DD C
    EE D

  8. #8
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Okay,
    So in your case you dont have a field that is distinct between rows, like in my example.

    You don't want to use temp tables.
    Well, to avoid that, i created this proc that uses a cursor, i don't like what it does because there are simpler and faster ways. Also if you dont want duplicates you should create a primary key on that table.

    Here goes:

    Code:
    create table Example(
    column1     varchar(10),
    column2     varchar(10)
    )
    
    insert into Example values ('AA','A')
    insert into Example values ('AA','A')
    insert into Example values ('BC','B')
    insert into Example values ('BC','B')
    insert into Example values ('DD','C')
    insert into Example values ('EE','D') 
    go
    
    
    create proc Get_duplicated
    as
    
    Select * from Example  
    
    
    declare get_dups cursor for
        select distinct * from Example e
        order by e.column1
        
        declare @column1 varchar(10)
        declare @column2 varchar(10)
        
    
        open get_dups
        
        fetch get_dups into @column1, @column2
        
        while @@sqlstatus = 0  
        begin
            
            if (select count(1) from Example where @column1 = column1 and @column2=column2) > 1 
            begin
                begin tran
                       delete from Example where @column1 = column1 and  @column2=column2
                       insert into Example values (@column1,@column2)
                commit tran
            end 
            
            
               
        fetch get_dups into @column1, @column2
        end
        close get_dups 
        deallocate cursor get_dups
    
    Select * from Example   order by column1 
    
    go
    exec Get_duplicated
    go 
         
    drop proc Get_duplicated
    drop table Example

    Hope it helps.

  9. #9
    Join Date
    Jun 2012
    Posts
    15

    Thumbs up

    Well thats awesome ! Actually i was wondering if it could be possible to do it without a primary key or a constraint and here i got an answer .. thanks for the post catarranus ...

  10. #10
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    You're welcome!
    By the way its Catarrunas....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •