SELECT Distinct with condition of other column value

Question

I have the schema built at this SQL Fiddle if you want to try out.

I have the following data

AutoID         Apartment      Flag       Description
======         =========      ====       ===========
1              1              NO         Device 1
2              1              NO         Device 2
3              1              NO         Device 3
4              2              NO         Device 4
5              2              NO         Device 5
6              3              NO         Device 6
7              3              NO         Device 7
8              3              YES        Device 8
9              3              NO         Device 9 

I'm trying to get the data with the following rule

1. Only select distinct Apartment value
2. IF Flag is YES then select that item for the distinct value

So if I run the SQL statement I would end up with

AutoID         Apartment      Flag       Description
======         =========      ====       ===========
1              1              NO         Device 1
4              2              NO         Device 4
8              3              YES        Device 8

I've been trying to play around with OVER() and PARTITION BY with little luck. Any help or guidance is greatly appreciated.

Answer 1

First step - assign order numbers in each group with the same apartment number

Second step - select only the first one

select AutoID, Apartment, Flag, Description from (
select AutoID, Apartment, Flag, Description, row_number() over (partition by apartment order by Flag desc) rnum 
from table1)
where rnum = 1

Answer 2

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY Apartment
                                   ORDER BY CASE WHEN [Flag] = 'YES' THEN 1 ELSE 2 END,
                                            [AutoID])
    FROM TableA
)
SELECT *
FROM CTE
WHERE RN = 1

Here is the modified sqlfiddle