SQL statement for when the conditions are met, then pull that value. If not, pull the fallback value

Question

I have this list with multiple records for each person.

I want to select a specific record from it for each person with a certain condition.

If that certain condition does not meet "all" the records for that person, then we pull the fallback values.

For example:

In this case, I want to pull the record for each person with Active Status = Y then the max date.

If the records does not meet these condition, like John, then it will pull the record with max date regardless of its Active Status.

The result should be:

Answer 1

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Person
                                   ORDER BY Status DESC, Date DESC) rn
    FROM yourTable t
)

SELECT Person, Status, Mood, Date
FROM cte
WHERE rn = 1;

The ORDER BY clause used with ROW_NUMBER above places yes status records before no records. Within each of those two groups, the latest date records are placed first.