Finding rows in SQL where changes but only certain changes while keeping others

I have this scenario where I want each occurrence of an active row to bring back that row in my result set and also inactive if there is only 1 inactive record for that IDENTIFIER and also if there are more than 1 active also show those. I’ve used Row_Number function and then in another query show where the row = ‘1’ but if I do that row 1s only come back and then I lose some of my desired results. To restate my issue is I want all active records to come back and only inactive where IDENTIFIER is unique. The row that is bold should not be shown in the results. 1 has 1 active record in the DB.
2 has 2 active and 1 inactive records.
3 has no active records.
4 has only 2 active records, no inactive.

Answer

You can use a windowed conditional count, this has the benfit of only scanning the table once

SELECT
    t.IDENTIFIER,
    t.DB_ID,
    t.Status
FROM (
    SELECT *,
        HasActive = COUNT(CASE WHEN t.Status = 'Active' THEN 1 END) OVER (PARTITION BY t.IDENTIFIER)
    FROM YourTable t
) t
WHERE t.Status = 'Active' OR t.HasActive = 0;