Selecting Rows by Max Date and something else

Hey stribor45 ,

To select rows by MAX(Date) and a secondary condition when there are ties, you can use techniques like row_number, rank, or filtering with a join/subquery, depending on your SQL dialect.

Method 1: ROW_NUMBER() with ORDER BY Multiple Columns

If you have a column to break ties (e.g., Status, Amount, Priority), use ROW_NUMBER() to pick just one:

WITH RankedData AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY ID
           ORDER BY Date DESC, Status DESC, Amount DESC
         ) AS rn
  FROM your_table
)
SELECT *
FROM RankedData
WHERE rn = 1;

You can replace Status DESC, Amount DESC with whatever additional logic makes a row preferable.

Method 2: RANK() for All Ties at Max Date, Then Filter

If you want to keep all rows that tie for the best Date and your secondary preference, use RANK():

WITH RankedData AS (
  SELECT *,
         RANK() OVER (
           PARTITION BY ID
           ORDER BY Date DESC, Status DESC
         ) AS rnk
  FROM your_table
)
SELECT *
FROM RankedData
WHERE rnk = 1;

This returns all top-ranked rows per ID.

Method 3: Subquery with MAX(Date), Then Join on Other Logic

If you don’t want window functions:

SELECT t.*
FROM your_table t
JOIN (
    SELECT ID, MAX(Date) AS MaxDate
    FROM your_table
    GROUP BY ID
) maxed ON t.ID = maxed.ID AND t.Date = maxed.MaxDate
WHERE t.Status = 'PreferredStatus'; -- or any other condition

This returns only rows with the max date and your custom logic.

Deciding “Which Row to Keep”

Use these heuristics based on your context:

• Pick the most recent DateTime + Highest Priority.

• Prefer a specific Status like 'Active' or 'Completed'.

• Use ISNULL(Column, 0) or custom scoring if the preference is complex.

You can also chain ORDER BY columns in the ROW_NUMBER() clause to incorporate multiple layers of preference.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam