SQL query that deletes Duplicate Rows

Question

Example

I need to figure out how to delete duplicate values (row 1 and row 3 refers to the same payment but the date is different). How can I delete one of the rows?

Answer 1

This query returns only one result per line.

SELECT
 id,
 status,
 processed_amt,
 balance_before,
 balance_after,
 MAX(process_date)
FROM
 your_table
GROUP BY id, status, processed_at, balance_before, balance_after

Any aggregation function will do.

--- EDIT ---

Or did you mean you want really to delete rows from your table?

In that case you could use method in example linked to comments.

WITH duplicates AS (
   SELECT 
       id, 
       ROW_NUMBER() OVER 
       (PARTITION BY id 
        ORDER BY process_date DESC) row_num 
    FROM your_table) 
DELETE FROM duplicates WHERE row_num, > 1;

But I would strongly recommend to have UNIQUE constraint for you ID column and not letting this scenario happen in first place.