SELECT Distinct with a condition for a different column value

Question

I have a schema built in this SQL Fiddle if you want to try it.

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 am trying to get data with the following rule

• Select only a single apartment price
• IF Flag - YES, then select this item for a single 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 am trying to play with OVER () and PARTITION BY with little luck. Any help or guidance is appreciated.

Answer 1

;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's the modified sqlfiddle

Answer 2

The first step is to assign order numbers in each group with the same apartment number

-

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