Delete duplicates based on Group By - SQL

indofraiser

EDIT: I think I now have the solution but need to do some more sense checking...

DELETE TBLFIRE_TEMP3 FROM TBLFIRE_TEMP3
LEFT OUTER JOIN (
   SELECT MIN(FireNo) as FireNo, ActionRef, FRADate, FIREUPRN
   FROM TBLFIRE_TEMP3 
   GROUP BY ActionRef, FRADate, FIREUPRN
) as KeepRows ON
   TBLFIRE_TEMP3.FireNo = KeepRows.FireNo
WHERE
   KeepRows.FireNo IS NULL

-############### Previous Comments ###############

I have a table which has duplicates in (based on three columns). I can find them and see them by doing the following and would then simply want to delete the duplicates (i.e. so all count(*) results are '1')

SELECT COUNT(*),ActionRef, FRADate, FIREUPRN
FROM TBLTempTable
GROUP BY ActionRef, FRADate, FIREUPRN

So I can see the count of how many times these groups occur. What I want to do is Delete the duplicates. I've tried the below but it deletes every row, even singular:

DELETE a FROM TblTempTable a JOIN
(
  SELECT ActionRef, FRADate, FIREUPRN
    FROM TblTempTable 
   GROUP BY ActionRef, FRADate, FIREUPRN
) d 
   ON (a.ActionRef = b.ActionRef
  AND a.FRADate = b.FRADate
AND a.FIREUPRN = b.FIREUPRN)

Based on the codes I've looked at the guide me I believe I am close but currently it deletes everything.

References: SQL- How can I remove duplicate rows? GROUP BY does not remove duplicates

-These are MySQL so not to relevant in the end:

select and delete rows within groups using mysql Find duplicate records in MySQL

Tim Schmelter

A simple solution is to use a CTE with ROW_NUMBER:

WITH Data AS
(
    SELECT RN  = ROW_NUMBER() OVER (PARTITION BY ActionRef, FRADate, FIREUPRN
                                    ORDER BY FRADate ASC),
           Cnt = COUNT(*) OVER (PARTITION BY ActionRef, FRADate, FIREUPRN),
           ActionRef, FRADate, FIREUPRN
    FROM TBLTempTable
)
DELETE FROM Data
WHERE RN > 1

This deletes all but one, it keeps the oldest FRADate. You need to change the ORDER BY in ROW_NUMBER to change this logic.

One advantage of a CTE is that you can change it easily to see what you're going to delete (or update). Therefore you just have to replace DELETE FROM Data with SELECT * FROM Data.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

sql delete based on group by

From Dev

Filter out the duplicates based on a group in SQL

From Dev

A query to delete duplicates with GROUP BY

From Dev

SQL DELETE group of records based on opposite group being empty

From Dev

Find duplicates based on two fields and delete them

From Dev

How to delete one of the duplicates based on another column?

From Dev

Delete duplicates in large dataset based on condition

From Dev

Delete pandas group based on condition

From Dev

Delete pandas group based on condition

From Dev

SQL Server : delete duplicates depending on 2 columns

From Dev

SQL Server : Find and Group Duplicates by "weak" criterias

From Dev

Using group by in a sql query for remuving duplicates

From Dev

How to find duplicates within a group - SQL 2008

From Dev

SQL Server : Find and Group Duplicates by "weak" criterias

From Dev

group by query in sql server returns duplicates

From Dev

SQL still have duplicates after Group By

From Dev

Returning duplicates based on one value in SQL

From Dev

SQL Query Advice - Updating records based on duplicates

From Dev

Remove duplicates based on two columns SQL

From Dev

Delete Duplicates in Array based on matching id. Rails

From Dev

Python - Delete duplicates in a dataframe based on two columns combinations?

From Dev

Oracle How delete duplicates based on multiples database columns and conditions

From Dev

Delete Duplicates in Array based on matching id. Rails

From Dev

group by and delete documents based on a field array size

From Dev

Delete a row based on the first value of the group

From Dev

Listing based on a particular SQL Group

From Dev

SQL - Group By based on optional flag

From Dev

T-SQL Delete half of duplicates with no primary key

From Dev

How to delete duplicates and leave one row in a table - sql

Related Related

  1. 1

    sql delete based on group by

  2. 2

    Filter out the duplicates based on a group in SQL

  3. 3

    A query to delete duplicates with GROUP BY

  4. 4

    SQL DELETE group of records based on opposite group being empty

  5. 5

    Find duplicates based on two fields and delete them

  6. 6

    How to delete one of the duplicates based on another column?

  7. 7

    Delete duplicates in large dataset based on condition

  8. 8

    Delete pandas group based on condition

  9. 9

    Delete pandas group based on condition

  10. 10

    SQL Server : delete duplicates depending on 2 columns

  11. 11

    SQL Server : Find and Group Duplicates by "weak" criterias

  12. 12

    Using group by in a sql query for remuving duplicates

  13. 13

    How to find duplicates within a group - SQL 2008

  14. 14

    SQL Server : Find and Group Duplicates by "weak" criterias

  15. 15

    group by query in sql server returns duplicates

  16. 16

    SQL still have duplicates after Group By

  17. 17

    Returning duplicates based on one value in SQL

  18. 18

    SQL Query Advice - Updating records based on duplicates

  19. 19

    Remove duplicates based on two columns SQL

  20. 20

    Delete Duplicates in Array based on matching id. Rails

  21. 21

    Python - Delete duplicates in a dataframe based on two columns combinations?

  22. 22

    Oracle How delete duplicates based on multiples database columns and conditions

  23. 23

    Delete Duplicates in Array based on matching id. Rails

  24. 24

    group by and delete documents based on a field array size

  25. 25

    Delete a row based on the first value of the group

  26. 26

    Listing based on a particular SQL Group

  27. 27

    SQL - Group By based on optional flag

  28. 28

    T-SQL Delete half of duplicates with no primary key

  29. 29

    How to delete duplicates and leave one row in a table - sql

HotTag

Archive