Sql To Delete The Duplicates In A Table

I have a table transaction which has duplicates. i want to keep the record that had minimum id and delete all the duplicates based on four fields DATE, AMOUNT, REFNUMBER, PARENTFOL

Solution 1:

It would probably be more efficient to do something like

DELETEFROM transaction t1
 WHEREEXISTS( SELECT1FROM transaction t2
                WHERE t1.date = t2.date
                  AND t1.refnumber = t2.refnumber
                  AND t1.parentFolderId = t2.parentFolderId
                  AND t2.id > t1.id )

Solution 2:

DELETEFROM transaction
      WHERE ID IN (
               SELECT ID
                 FROM (SELECT ID,
                          ROW_NUMBER () OVER (PARTITIONBYdate
                                                          ,amount
                                                          ,refnumber
                                                          ,parentfolderid
                                                ORDERBY ID) rn
                                              FROM transaction)
                WHERE rn <>1);

I will try like this

Solution 3:

I would try something like this:

DELETE transaction 
FROM transaction
LEFTOUTERJOIN 
   (
       SELECTMIN(id) as id, date, amount, refnumber, parentfolderid 
       FROM transaction
      GROUPBYdate, amount, refnumber, parentfolderid
   ) as validRows 
ON transaction.id = validRows.id
WHERE validRows.id ISNULL