如何从重复分组记录中筛选日期最大的唯一记录

Got it, so you have multiple records grouped by the first few fields (like A/B/C or D/E/F) and you want to retain only the one with the most recent date in each group. Let's break down how to do this in a few common tools—pick the one that fits your workflow!

SQL Solution #

If you're working with a database, window functions are the cleanest way to tackle this. Using ROW_NUMBER() lets you rank each row in a group by date, then you just keep the top-ranked entry.

Here's an example query assuming your table is named records with columns col1, col2, col3, record_date:

WITH ranked_records AS (
    SELECT 
        col1, col2, col3, record_date,
        ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY record_date DESC) AS rn
    FROM records
)
SELECT col1, col2, col3, record_date
FROM ranked_records
WHERE rn = 1;

• PARTITION BY col1, col2, col3 groups rows by your non-date fields.
• ORDER BY record_date DESC ranks the latest date first in each group.
• Filtering where rn = 1 grabs only the most recent record per group.

Python (Pandas) Solution #

If you're using pandas to work with this data in a DataFrame, there are two straightforward approaches:

Method 1: Groupby + idxmax #

This finds the index of the maximum date in each group, then selects those rows directly:

import pandas as pd

# Assume your DataFrame is named df with columns ['col1', 'col2', 'col3', 'date']
latest_records = df.loc[df.groupby(['col1', 'col2', 'col3'])['date'].idxmax()]

Method 2: Sort and Drop Duplicates #

Sort the DataFrame by date (newest first), then drop duplicates while keeping the first occurrence per group:

latest_records = df.sort_values('date', ascending=False).drop_duplicates(subset=['col1', 'col2', 'col3'])

Both methods give the same result—go with whichever feels more readable to you!

Excel Solution #

For those working in Excel, use MAXIFS to flag the latest date per group, then filter accordingly:

1. Add a helper column (e.g., column E) with this formula to get the max date for the current row's group:

   =MAXIFS(D:D, A:A, A2, B:B, B2, C:C, C2)
   

2. Filter the rows where the date (column D) matches the value in the helper column (column E).
3. Copy the filtered rows to a new sheet to get your clean, unique latest records.

All these approaches will leave you with exactly one record per group—the most recent one.

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