Delete except max records on duplicate

Question

I have table like below format,

Item_Txn       Item_Name
101            Mouse
102            Mouse
103            Mouse
104            Keyboard
105            CPU
106            Monitor
107            Monitor

I want to delete duplicate items except max Item_Txn. For eg., Mouse is duplicate items(3 times). I want to delete Mouse record except (103, Mouse).

This is called DeDup. You will find plenty of techniques online as well. — Raj More, Jun 22 '16 at 13:10
Possible duplicate of [T-SQL: Deleting all duplicate rows but keeping one](http://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one) — Tab Alleman, Jun 22 '16 at 19:42

score 4 · Answer 1 · answered Jun 22 '16 at 12:33

4

For SQL Server 2008 and newer:

;WITH cte AS
(
    SELECT      Item_Txn, Item_Name,
                ROW_NUMBER() OVER (PARTITION BY Item_Name ORDER BY Item_Txn DESC) AS RowNumber
    FROM        my_table
)

DELETE FROM cte
    WHERE RowNumber > 1

answered Jun 22 '16 at 12:33

Code Different

90,614
16
144
163

score 2 · Answer 2 · edited Jun 22 '16 at 12:39

2

DELETE a
FROM   my_table a
WHERE  EXISTS (SELECT *
               FROM   my_table b
               WHERE  a.Item_Name = b.Item_Name
                 AND  b.Item_Txn > a.Item_Txn);

edited Jun 22 '16 at 12:39

gofr1

15,741
11
42
52

answered Jun 22 '16 at 12:31

Robert Kock

5,795
1
12
20

score 1 · Answer 3 · answered Jun 22 '16 at 12:33

Try this:

DELETE  FROM MyTable
WHERE   Item_Txn IN (
    SELECT  K.Item_Txn
    FROM    ( SELECT    Item_Txn ,
                        ROW_NUMBER() OVER ( PARTITION BY Item_Name ORDER BY Item_Txn DESC ) AS RN
              FROM      MyTable
            ) AS K
    WHERE   K.RN > 1 );

score 1 · Answer 4 · answered Jun 22 '16 at 12:34

1

Try this,

delete from table 
where Item_Txn not in
         (select max(Item_Txn) from table group by Item_Name)

answered Jun 22 '16 at 12:34

DineshDB

5,998
7
33
49

gofr1 · Answer 5 · 2016-06-22T12:40:28.900

1

DELETE t
FROM YourTable t
OUTER APPLY (
    SELECT MAX(Item_Txn) as Item_Txn
    FROM YourTable  t1
    WHERE t1.Item_Name = t.Item_Name
    ) as p
WHERE p.Item_Txn != t.Item_Txn

That query will left only:

103 Mouse
104 Keyboard
105 CPU
107 Monitor

edited Jun 22 '16 at 12:40

answered Jun 22 '16 at 12:35

gofr1

15,741
11
42
52

score 1 · Answer 6 · answered Jun 22 '16 at 12:35

You can do it using an Intermediate Subquery

    DELETE FROM `table`
    WHERE id NOT IN (
      SELECT id
      FROM (
        SELECT id
        FROM `table`
        ORDER BY id DESC
        LIMIT 1 -- keep this many records
      ) foo
    );

Above is for MySQL

This is for SQL

    DELETE FROM chat WHERE id NOT IN 
       (SELECT TOP 50 id FROM chat ORDER BY id DESC)

score 1 · Answer 7 · answered Jun 22 '16 at 12:53

    ;WITH CTE AS
    (
        SELECT MAX(Item_Txn)Item_Txn, Item_Name FROM ITEM GROUP BY Item_Name
    )
    DELETE t
    FROM ITEM t
    WHERE EXISTS
    (
        SELECT 1 FROM CTE WHERE t.Item_Name = CTE.Item_Name AND t.Item_Txn <> CTE.Item_Txn
    )

Delete except max records on duplicate

7 Answers7