locked
remove duplicates based on three columns RRS feed

  • Question

  • Hello. How can I remove duplicates where all three columns have the same value in the rows. Thanks. 
    Tuesday, October 25, 2016 1:58 AM

Answers

  • Would this work?

    declare @tablename table(col1 int, col2 int, col3 int)
    insert into @tablename (col1, col2, col3) values(1,2,3)
    insert into @tablename (col1, col2, col3) values(4,5,6)
    insert into @tablename (col1, col2, col3) values(7,7,6)
    insert into @tablename (col1, col2, col3) values(8,8,8)
    insert into @tablename (col1, col2, col3) values(9,9,8)
    insert into @tablename (col1, col2, col3) values(9,9,9)
    insert into @tablename (col1, col2, col3) values(9,9,9)--dup
    select col1, col2, col3, count(*) from @tablename  where col1=col2 and col2=col3
    group by col1, col2, col3
    having count(*)>1
    delete from t
    from @tablename  t
    join (select col1, col2, col3, count(*) count from @tablename  where col1=col2 and col2=col3
    group by col1, col2, col3
    having count(*)>1
    ) as X
    on t.col1=X.col1
    and t.col2=X.col2
    and t.col3=X.col3
    select * from @tablename

    Tuesday, October 25, 2016 2:19 AM
  • I blogged about it long time ago

    http://dimantdatabasesolutions.blogspot.co.il/2007/02/dealing-with-duplicates.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 26, 2016 6:45 AM
    Answerer

All replies

  • Would this work?

    declare @tablename table(col1 int, col2 int, col3 int)
    insert into @tablename (col1, col2, col3) values(1,2,3)
    insert into @tablename (col1, col2, col3) values(4,5,6)
    insert into @tablename (col1, col2, col3) values(7,7,6)
    insert into @tablename (col1, col2, col3) values(8,8,8)
    insert into @tablename (col1, col2, col3) values(9,9,8)
    insert into @tablename (col1, col2, col3) values(9,9,9)
    insert into @tablename (col1, col2, col3) values(9,9,9)--dup
    select col1, col2, col3, count(*) from @tablename  where col1=col2 and col2=col3
    group by col1, col2, col3
    having count(*)>1
    delete from t
    from @tablename  t
    join (select col1, col2, col3, count(*) count from @tablename  where col1=col2 and col2=col3
    group by col1, col2, col3
    having count(*)>1
    ) as X
    on t.col1=X.col1
    and t.col2=X.col2
    and t.col3=X.col3
    select * from @tablename

    Tuesday, October 25, 2016 2:19 AM
  • I blogged about it long time ago

    http://dimantdatabasesolutions.blogspot.co.il/2007/02/dealing-with-duplicates.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 26, 2016 6:45 AM
    Answerer