SQL View Conditional Selection

Question

I am trying to create a view that pulls a particular record. The problem I am running into is that there are 2 records to choose from and a status flag that is either 1 or 2. It should pull the 1 record if it exists, and if not the 2 record.

Is this possible from a view?

Answer 1

try sorting by status value, or group and return the min

Answer 2

pull select top 1 and order it by the status flag.

Answer 3

Select B.*
FROM
    (Select 
       ID
       ,MIN(Flag) Flag
    From TableName
    Group by ID) A
    LEFT JOIN TableName B on A.ID=B.ID and A.Flag=B.Flag

Answer 4

select * from table
where Status = 1

union

select * from table t
where status = 2 
    and not exists (select * from table t2 where t.id = t2.id and Status = 1)

Note that this approach works best when you have an id column to compare against. It is a different flavor of what Faiz wrote.