SQL多表关联查询：如何仅返回用户最新日期的单条记录

Hey James, I get it—trying to wrangle that GROUP BY clause to pull just one latest record per user is frustrating. Let's break down why your current query isn't working, then swap in a cleaner approach that gets you exactly what you need.

The Problem With Your Current Query #

When you use MAX(e.MEPE_EFF_DT) alongside non-aggregated fields (like s.FIRST_NAME, a.ADDR1, etc.), most SQL databases require you to include all those non-aggregated fields in the GROUP BY clause. This forces the query to group rows where all those fields are identical—so if a user has multiple entries with different addresses or dates, you'll end up with multiple rows instead of just the latest one.

The Solution: Window Functions #

Window functions (like ROW_NUMBER()) are made for exactly this scenario—grabbing the top record per group (in this case, per user ID). Here's how to rewrite your query:

WITH RankedUserRecords AS (
    SELECT 
        s.ID, 
        s.FIRST_NAME, 
        s.LAST_NAME, 
        a.ADDR1, 
        a.CITY, 
        a.STATE, 
        a.ZIP, 
        e.EFF_DT, 
        e.TERM_DT, 
        e.MEPE_EFF_DT,
        -- Assign a unique rank to each user's records, starting with the latest date
        ROW_NUMBER() OVER (
            PARTITION BY s.ID 
            ORDER BY e.MEPE_EFF_DT DESC
        ) AS record_rank
    FROM SUBSC s 
    INNER JOIN ADDR a ON s.SID = a.SID 
    INNER JOIN ELIG e ON e.EID = s.EID 
    WHERE a.GID = 99 AND e.ELIG_IND = 'Y'
)
-- Only keep the top-ranked (latest) record for each user
SELECT 
    ID, FIRST_NAME, LAST_NAME, ADDR1, CITY, STATE, ZIP, EFF_DT, TERM_DT, MEPE_EFF_DT
FROM RankedUserRecords
WHERE record_rank = 1
ORDER BY LAST_NAME ASC;

How This Works #

1. CTE (RankedUserRecords): This common table expression first pulls all the data you need, then adds a record_rank column.
   • PARTITION BY s.ID: Splits the data into groups where each group is a single user.
   • ORDER BY e.MEPE_EFF_DT DESC: Sorts each user's records from newest to oldest.
   • ROW_NUMBER(): Assigns a number to each record in the group—1 for the latest, 2 for the next, etc.
2. Final Select: We filter for record_rank = 1 to grab only the latest record per user, no messy GROUP BY required.

Bonus: Handling Ties #

If a user has multiple records with the exact same MEPE_EFF_DT and you want to keep all of them, replace ROW_NUMBER() with RANK(). This will give the same rank to records with matching dates, so you'll get all the latest entries instead of just one.

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