Sql-server – Select distinct value from row if condition exists

sql servert-sql

I am new to SQL and I am having trouble constructing a SELECT statement.

What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I would like to select distinct material with no condition of location.

Is there a way to make this select statement 1 query or do I need to break it up in selecting the distinct condition true values and selecting the distinct condition false values?

Sample data:

+------+------+----------+
| PART | TYPE | Location |
+------+------+----------+
| A    |    1 | A1       |
| A    |    2 | A1       |
| A    |    1 | A2       |
| A    |    2 | A2       |
| B    |    1 | A2       |
| B    |    1 | A3       |
+------+------+----------+

Best Answer

How about a little 2 in 1 using CTE?

;with cte as (
    select top 1 loc = 1
    from parts 
    where Location = 'A1'
)
select distinct Part --, Type, Location
from parts left join cte on 1=1
where loc is null or Location = 'A1';
Related Question