PostgreSQL删除col2、col3重复行，保留col_date_updated最新记录

Got it, let's solve this problem where you need to keep only the latest row (based on col_date_updated) for each unique pair of col2 and col3. Here are practical approaches for common database systems:

Using Window Functions (PostgreSQL, MySQL 8.0+, SQL Server, etc.) #

This is the cleanest and most efficient method for modern databases that support window functions. We'll use ROW_NUMBER() to rank rows within each col2/col3 group, then pick the top-ranked (most recent) row:

WITH ranked_records AS (
    SELECT
        col_pk,
        col1,
        col2,
        col3,
        col4,
        col_date_updated,
        -- Assign rank: 1 = most recent in each (col2, col3) group
        ROW_NUMBER() OVER (
            PARTITION BY col2, col3 
            ORDER BY col_date_updated DESC
        ) AS row_rank
    FROM your_table_name
)
SELECT col_pk, col1, col2, col3, col4, col_date_updated
FROM ranked_records
WHERE row_rank = 1;

How it works: #

• PARTITION BY col2, col3 groups rows that have identical values in both columns.
• ORDER BY col_date_updated DESC ensures the most recent row gets the rank 1.
• We filter for row_rank = 1 to keep only the latest entry per group.

For your sample data, this query will return exactly the expected result: rows 3 and 4.

For Older MySQL Versions (Pre-8.0) #

If you're using MySQL 5.x which doesn't support window functions, use a subquery to find the latest timestamp per col2/col3 pair, then join back to the original table:

SELECT t.*
FROM your_table_name t
INNER JOIN (
    -- Get the latest update time for each (col2, col3) group
    SELECT col2, col3, MAX(col_date_updated) AS latest_update
    FROM your_table_name
    GROUP BY col2, col3
) t_latest 
    ON t.col2 = t_latest.col2 
    AND t.col3 = t_latest.col3 
    AND t.col_date_updated = t_latest.latest_update;

Note: #

If multiple rows in a group have the exact same latest timestamp, this query will return all of them. To pick just one (e.g., the one with the highest col_pk), adjust the subquery to select the max primary key instead:

SELECT t.*
FROM your_table_name t
INNER JOIN (
    SELECT col2, col3, MAX(col_pk) AS latest_pk
    FROM your_table_name
    WHERE (col2, col3, col_date_updated) IN (
        SELECT col2, col3, MAX(col_date_updated)
        FROM your_table_name
        GROUP BY col2, col3
    )
    GROUP BY col2, col3
) t_latest 
    ON t.col_pk = t_latest.latest_pk;

Deleting Duplicate Rows (Optional) #

If you need to permanently remove the duplicate rows instead of just querying the unique ones, adapt the window function approach:

-- PostgreSQL example
WITH ranked_records AS (
    SELECT
        col_pk,
        ROW_NUMBER() OVER (
            PARTITION BY col2, col3 
            ORDER BY col_date_updated DESC
        ) AS row_rank
    FROM your_table_name
)
DELETE FROM your_table_name
WHERE col_pk IN (SELECT col_pk FROM ranked_records WHERE row_rank > 1);

内容的提问来源于stack exchange，提问作者Dru