rifttech · March 6, 2018 16:52
diff --git a/remove duplicates.sql b/remove duplicates.sql
 --The following statement uses a suquery to delete duplicate rows and keep the row with the lowest id.
 DELETE FROM basket
 WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY fruit
        ORDER BY  id ) AS row_num
        FROM basket ) t
        WHERE t.row_num > 1 );
        
 /*
 In this example, the subquery returned the duplicate rows except for the first row in the duplicate group. And the outer DELETE statement deleted the duplicate rows returned by the subquery.

 If you want to keep the duplicate row with highest id, just change the order in the subquery:*/
 DELETE FROM basket
 WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY fruit
        ORDER BY  id DESC ) AS row_num
        FROM basket ) t
        WHERE t.row_num > 1 );
 --In case you want to delete duplicate based on values of multiple columns, here is the query template:
 DELETE FROM table_name
 WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY column_1,
         column_2
        ORDER BY  id ) AS row_num
        FROM table_name ) t
        WHERE t.row_num > 1 );
 --In this case, the statement will delete all rows with duplicate values in the column_1 and column_2 columns.
No results found