SQL技术问询：获取指定Client下各Source对应的最新Source_ID

Hey there, let's fix your query to get the exact results you're looking for! The issue with your attempted query is that your subquery only groups by Source—but you need to group by both Client and Source because you want the latest record for each combination of these two fields, not just each Source across all Clients.

Here's the corrected SQL: #

SELECT 
    t.Client, 
    t.Source, 
    CAST(t.Source_ID AS bigint) AS Source_ID, 
    t.date_added
FROM table1 t
INNER JOIN (
    -- Get the latest date_added for each (Client, Source) pair
    SELECT 
        Client, 
        Source, 
        MAX(date_added) AS newest_date
    FROM table1
    WHERE Client IN ('2020937') 
      AND TRIM(Source) IN ('A', 'B')
    GROUP BY Client, Source
) latest_records 
    ON t.Client = latest_records.Client
    AND t.Source = latest_records.Source
    AND t.date_added = latest_records.newest_date
ORDER BY t.Client, t.Source;

Why your original query failed: #

Your subquery grouped only by Source, which would return the newest date_added for Source A or B across all Clients in the table. If, for example, another Client has a more recent date_added for Source B than Client 2020937, your join condition A.date_added = B.Newest would never match any records for Client 2020937—hence the empty result set.

What this corrected query does: #

1. The subquery filters down to your target Client and Sources first, then groups by both Client and Source to get the most recent date_added for each pair.
2. We join this back to the original table using all three fields (Client, Source, date_added) to fetch the full record corresponding to that latest date.
3. This ensures you get exactly one row per (Client, Source) pair with the latest Source_ID and its date_added.

Expected Output: #

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