SQL First Match or just First

Because this is part of a larger SQL SELECT statement, I want a wholly SQL q开发者_Python百科uery that selects the first item matching a criteria or, if no items match the criteria, just the first item.

I.e. using Linq I want:

Dim t1 = From t In Tt

Dim t2 = From t In t1 Where Criteria(t)

Dim firstIfAny = From t In If(t2.Any, t2, t1) Take 1 Select t

Because If is not part of Linq, LinqPad doesn't show a single SQL statement, but two, the second depending upon whether the Criteria matches any of the Tt values.

I know it will be SELECT TOP 1 etc. and I can add ORDER BY clauses to get the specific first one I want, but I'm having trouble thinking of the most straightforward way to get the first of two criteria. _{(It was at exactly this point when I was able to solve this myself.)}

Seeing as I don't see an existing question for this, I will let it stand. I'm sure someone else will see the answer quickly.

select top 1 *
from (
    select top 1 *, 1 as Rank from MyTable where SomeColumn = MyCriteria
    union all
    select top 1 *, 2 as Rank from MyTable order by MyOrderColumn
) a 
order by Rank

I've gone with this:

SELECT TOP 1 *
FROM MyTable
WHERE SomeColumn = MyCriteria
 OR NOT (EXISTS (SELECT NULL FROM MyTable WHERE SomeColumn = MyCriteria))
ORDER BY MyOrdering

My actual SomeColumn = MyCriteria is rather more complex of course, as well as other unrelated where clauses.