Editing Finding and deleting duplicates (section)

====Finding and Deleting Duplicates====
To ensure only the most recent record for each primary key is retained, duplicates can be identified and removed using the following steps:

1. **Identify Duplicates**:

Use the `ROW_NUMBER()` function to assign a rank to each row for the same primary key, ordered by `kptinsertedat` or `filesourcekey`. For example:

<syntaxhighlight lang="sql" line>
WITH ranked_rows AS (
    SELECT 
        "ACC45ID",
        filesourcekey,
        kptinsertedat,
        ROW_NUMBER() OVER (PARTITION BY "ACC45ID" ORDER BY kptinsertedat DESC) AS r
    FROM indici_staging.accidents
)
SELECT *
FROM ranked_rows
WHERE r > 1;
</syntaxhighlight>

2. **Delete Duplicates**:

Once duplicates are identified, rows where <code>r > 1</code> can be deleted while retaining the most recent record:

<syntaxhighlight lang="sql" line>
WITH ranked_rows AS (
    SELECT 
        "ACC45ID",
        filesourcekey,
        ROW_NUMBER() OVER (PARTITION BY "ACC45ID" ORDER BY kptinsertedat DESC) AS r
    FROM indici_staging.accidents
)
DELETE FROM indici_staging.accidents
WHERE ( "ACC45ID", filesourcekey ) IN (
    SELECT "ACC45ID", filesourcekey
    FROM ranked_rows
    WHERE r > 1
);
</syntaxhighlight>

This ensures that only the most recent record for each primary key remains in the staging table, ready to be inserted into the target table.