How to Delete Duplicate Records in Sql

How can I remove duplicate rows?

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

In case you have a GUID instead of an integer, you can replace

MIN(RowId)

with

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

Delete Duplicate Rows in SQL

You can use a Common Table Expression to delete the duplicates:

WITH Cte AS(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY PersonAliasId, StartDateTime, GroupId 
                                ORDER BY ModifiedDateTIme DESC)
    FROM Attendance
)
DELETE FROM Cte WHERE Rn > 1;

This will keep the most recent record for each PersonAliasId - StartDateTime - GroupId combination.

How can I delete one of two perfectly identical rows?

One option to solve your problem is to create a new table with the same schema, and then do:

INSERT INTO new_table (SELECT DISTINCT * FROM old_table)

and then just rename the tables.

You will of course need approximately the same amount of space as your table requires spare on your disk to do this!

It's not efficient, but it's incredibly simple.

Delete duplicate records leaving only the latest one

You can use Count with partition by to find and insert the duplicate records into answersArchive table like following.

1- Find Duplicate and Insert into answersArchive table

--copy the duplicate records
;WITH cte 
     AS (SELECT id, 
                answer, 
                country_id, 
                question_id, 
                updated, 
                Count(*) 
                  OVER( 
                    partition BY question_id ) ct 
         FROM   answers 
         WHERE  country_id = 15) 
INSERT INTO answersarchive 
SELECT id, 
       answer, 
       country_id, 
       question_id, 
       updated 
FROM   cte 
WHERE  ct > 1 --Give you duplicate records 

2- Delete all duplicates except the latest one.

You can use CTE to delete the records. To find the duplicate records you can use ROW_NUMBER() with PARTITION BY question_id like following query.

;WITH cte 
     AS (SELECT id, 
                answer, 
                country_id, 
                question_id, 
                updated, 
                Row_number() 
                  OVER( 
                    partition BY question_id 
                    ORDER BY updated DESC) RN 
         FROM   answers 
         WHERE  country_id = 15) 

DELETE FROM cte 
WHERE  rn > 1 

SQL query to remove duplicate records without primary key, keeping the most recent

For your example, you can just use aggregation:

select ACC_PART_MAT, min(TX_DATE) as TX_DATE
from t
group by ACC_PART_MAT;

If you actually wanted to delete rows from a table, you can use an updatable CTE -- but be careful because this changes the table:

with todelete as (
      select t.*,
             row_number() over (partition by ACC_PART_MAT order by TX_DATE asc) as seqnum
      from t
     ) 
delete from todelete
    where seqnum > 1;

How to delete duplicate records in SQL?

You can delete duplicates using i.e. ROW_NUMBER():

with duplicates as
(
    select
        *
        ,ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, age ORDER BY FirstName) AS number
    from yourTable
)
delete 
from duplicates
where number > 1

Each row where number is bigger than 1 is a duplicate.

How to remove duplicate MySQL records (but only leave one)

You can use this to keep the row with the lowest id value

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id > e2.id AND e1.email = e2.email;

this an example link link 1

or you can change > to < for keep the highest id

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id < e2.id AND e1.email = e2.email;

this an example link link 2

---

Related Topics