how to delete duplicate in all rows ?

Question

how to delete duplicate in all rows ?

example:              

column : number             address   before               number          address       after                         

                 1                   street1                                1                street1                                           

                 2                   street2                                2                street2

                 2                   street2                                3                  street4

                    3                     street4                               

help query                                                                                                           

Answer 1

If you are using SS 2005 or greater, then use the function ROW_NUMBER to identify those rows.

with R as (
select row_number() over(partition by number order by (select NULL)) as rn
from T
)
delete R
where rn > 1;

As always, make a db backup before applying changes in batches.

---

AMB

Some guidelines for posting questions...

Answer 2

Pretty good example here:

http://support.microsoft.com/kb/139444

---

Chuck

Answer 3

;with cte as

(

select *,row_number()over(partition by number,address order by id) as r  from yrtable

)

delete from cte where r>1

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 4

Hi Friend,

Just try with this:

 ---------------------------------------------------------------------------------

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Kumar')
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Kumar')
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Rahul')

 --Select * from @table

SET NOCOUNT ON
SET ROWCOUNT 1

WHILE 1 = 1
   BEGIN
      DELETE   FROM @table
      WHERE    data IN (SELECT  data
                               FROM    @table
                               GROUP BY data
                               HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
         BREAK ;
   END
SET ROWCOUNT 0

--Select * from @table

 ---------------------------------------------------------------------------------------------

This will remove all the duplicates from a table.

---

Please Mark as Answer if my post solved your problem and Vote As Helpful if the post was useful. ====================Blogs: www.sqlserver2005forum.blogspot.com ==============

Answer 5

Hi Friend,

Just try with this:

 ---------------------------------------------------------------------------------

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Kumar')
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Kumar')
INSERT INTO @table VALUES ('Rahul')
INSERT INTO @table VALUES ('Vairagi')
INSERT INTO @table VALUES ('Rahul')

 --Select * from @table

SET NOCOUNT ON
SET ROWCOUNT 1

WHILE 1 = 1
   BEGIN
      DELETE   FROM @table
      WHERE    data IN (SELECT  data
                               FROM    @table
                               GROUP BY data
                               HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
         BREAK ;
   END
SET ROWCOUNT 0

--Select * from @table

 ---------------------------------------------------------------------------------------------

This will remove all the duplicates from a table.

---

Please Mark as Answer if my post solved your problem and Vote As Helpful if the post was useful. ====================Blogs: www.sqlserver2005forum.blogspot.com ==============

Here you are deleting one row at a time, so think about performance if you need to delete thousands of rows. On the other hand, the use of SET ROWCOUNT will not affect INSERT / UPDATE / DELETE in future versions of SQL Server.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

---

AMB

Some guidelines for posting questions...

Answer 6

One comment - if you added a primary key to your table it would make this a lot easier.  All tables should have some sort of unique key

---

Chuck

Answer 7

thank you Rahul Vairagi and Hunchback yours solutions