SQL分组获取每组最后一条记录：按BLMASKODU取最新BLKODU

Hey there! Let's figure out how to get the latest BLKODU for each BLMASKODU in your CARI_NOTLAR table—especially since you've got over 1000 unique BLMASKODU values and counting, we need a solution that scales well.

Here are the best approaches, depending on your database setup:

Method 1: Using Window Functions (Recommended for Large/Growing Data) #

Window functions like ROW_NUMBER() are the most efficient and scalable choice here. They let you group your data by BLMASKODU, rank entries by BLKODU (descending, so the newest/highest value gets top rank), then filter to keep only the top entry per group.

Here's the SQL:

SELECT BLMASKODU, BLKODU
FROM (
    SELECT 
        BLMASKODU, 
        BLKODU,
        ROW_NUMBER() OVER (PARTITION BY BLMASKODU ORDER BY BLKODU DESC) AS row_rank
    FROM CARI_NOTLAR
) ranked_data
WHERE row_rank = 1;

Why this works: #

• PARTITION BY BLMASKODU splits your table into separate groups for each unique BLMASKODU value.
• ORDER BY BLKODU DESC ensures the largest (newest) BLKODU in each group gets a rank of 1.
• Filtering WHERE row_rank = 1 gives you exactly one entry per BLMASKODU: the latest one.

This method is optimized for large datasets in modern databases (MySQL 8+, PostgreSQL, SQL Server, etc.) and will handle your growing BLMASKODU count smoothly.

Method 2: Using MAX() with a Self-Join (For Older Databases) #

If you're using an older database version that doesn't support window functions, this approach works too. We first find the highest BLKODU for each BLMASKODU, then join back to the original table to get the full row.

SELECT main.BLMASKODU, main.BLKODU
FROM CARI_NOTLAR main
INNER JOIN (
    SELECT BLMASKODU, MAX(BLKODU) AS latest_blkodu
    FROM CARI_NOTLAR
    GROUP BY BLMASKODU
) latest_entries 
    ON main.BLMASKODU = latest_entries.BLMASKODU 
    AND main.BLKODU = latest_entries.latest_blkodu;

Important Note: #

This assumes BLKODU is unique per BLMASKODU (no duplicate BLKODU values for the same BLMASKODU). If duplicates exist, you might get multiple rows per group—so the window function method is safer in that case.

Key Assumption to Verify: #

We’re assuming a higher BLKODU value means a newer entry. If your table has a timestamp column (like CREATED_DATE) that better reflects when entries were added, replace ORDER BY BLKODU DESC with ORDER BY CREATED_DATE DESC in the window function method for more accuracy:

SELECT BLMASKODU, BLKODU
FROM (
    SELECT 
        BLMASKODU, 
        BLKODU,
        ROW_NUMBER() OVER (PARTITION BY BLMASKODU ORDER BY CREATED_DATE DESC) AS row_rank
    FROM CARI_NOTLAR
) ranked_data
WHERE row_rank = 1;

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