Q: SQL query to retrieve value or catch all I'm dealing with a table in the following form:
  A        B
  ------   -----------
   1       value1
   2       value2
   3       value3
  -1       value4

In this table, the value -1 indicates a catch all, if there's no other match for A in the column. This means, a query for A = 2 should return a single record for which value2 is the value of column B. If the table is queried for, let's say, A = 6, then the value for B should be value4 (because it's the value associated with the catch all).
What's the "best" query to achieve this? Is there a better solution? I've scripted a small setup example in SQLFiddle, if that helps.
The database is SQL Server. 
Can you help? Many thanks.

A: select top (1) A, B
from (
  select A, B, 0 as priority from t where A = @value
  union
  select A, B, 1             from t where A = -1
) foo
order by priority


A: DECLARE @param int
SET @param = 6

SELECT * 
FROM test
WHERE a = @param OR (
    a = -1 AND @param NOT IN (SELECT a FROM test)
)

Replace @param = 6 with @param = 2 to test again

A: In MySQL, this would be trivial:
SELECT b FROM test WHERE a = @var or a = -1 ORDER BY a DESC LIMIT 1;

However, MSSQL doesn't have such a trick built in--you would need to add a stored procedure to limit it properly.
EDIT
It seems that in 2005, they added some paging functions:
SELECT TOP 1 b FROM test WHERE a = @var or a = -1 ORDER BY a DESC; 

should work.
All that said, this sounds like an issue of poor design; I would look at the application that needs this and see if there wasn't a cleaner way to achieve a default value.

A: Here's how I would write the query:
SELECT TOP 1 B 
  FROM mytable
 WHERE A IN (2,-1)
 ORDER BY CASE WHEN A = -1 THEN 1 ELSE 0 END, B;

This statement has a hardcoded search argument of "2" (as in your example).
You would substitute your search argument in place of the hardcoded "2", obviously.

The CASE expression in the ORDER BY clause makes sure that the "catch all" value of -1 is a "last resort", the -1 will be the LAST value in the list, any other any value will come BEFORE the -1 in the sort.
 A  expr
--  ---- 
-2     0
-1     1 
 0     0
 1     0
 2     0

So, when I ORDER BY expr we are guaranteed that a value of -1 will be LAST in the list. After the resultset is sorted, the TOP 1 will return no more than 1 row. So, the value associated with the -1 "catch all" value will only be returned if no other matching value is found.

A: if exists(select * from YourTable WHERE A=6)
  select * from YourTable WHERE A=6
else
  select * from YourTable WHERE A<0

