MySQL获取每组中最新记录（同一日期含多条数据场景）

Hey there! Let's figure out how to pull the latest record for each customer—where "latest" means the most recent transaction date first, and if there are multiple transactions on that date, we pick the one with the highest row_id.

Sample Input Data #

row_id cust txn_dt     txn_amount
-------------------------------------
1      1    31-01-2018 3000
2      1    04-02-2018 4000
3      1    04-02-2018 6000
4      2    29-01-2018 2500
5      2    02-02-2018 3900
6      1    01-02-2018 5000
7      1    01-02-2018 3900

Expected Output #

row_id cust txn_dt     txn_amount
-------------------------------------
3      1    04-02-2018 6000
5      2    02-02-2018 3900

Solution & Explanation #

The cleanest way to handle this is using window functions (supported in most modern SQL dialects like PostgreSQL, MySQL 8+, SQL Server, etc.). We'll rank each customer's transactions to prioritize the latest date and highest row_id, then pick the top-ranked record per customer.

Here's the SQL query:

WITH ranked_transactions AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY cust 
            ORDER BY txn_dt DESC, row_id DESC
        ) AS record_rank
    FROM your_transaction_table
)
SELECT row_id, cust, txn_dt, txn_amount
FROM ranked_transactions
WHERE record_rank = 1;

Let's break this down:

• CTE ranked_transactions: The ROW_NUMBER() function assigns a unique rank to each row grouped by customer (PARTITION BY cust). We order first by txn_dt DESC (so the most recent date gets rank 1) and then by row_id DESC (so if multiple transactions share the latest date, the one with the highest row_id takes priority).
• Final Select: We filter for rows where record_rank = 1—this gives us exactly one record per customer, which is the latest one we need.

If you're working with an older SQL version that doesn't support window functions, you can use a subquery to first find the max transaction date per customer, then the max row_id for that customer-date combination. But the window function method is more efficient and readable.

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