CODEWITHSUNDEEP
sqlsql-server-2005view
Dustin Laine
Dustin Laine

Reputation: 38503

SQL View Conditional Selection

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?

Upvotes: 0

Views: 443

Answers (4)

Jeff S
Jeff S

Reputation: 7484

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.

Upvotes: 0

Fosco
Fosco

Reputation: 38506

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

Upvotes: 0

Faiz
Faiz

Reputation: 5453

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

Upvotes: 0

Beth
Beth

Reputation: 9607

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

Upvotes: 3

Related Questions