Run the same query twice, but with one different WHERE clause

Question

Sorry the simplicity of my question, I have a brain navel:

Here is my table layout:

Foo( FooId, BarDate dateTime, Baz int )

Given this request

SELECT
    MAX( Foo.BarData )
FROM
    Foo
WHERE
    Foo.Baz = 2
    AND
    Foo.BarData < @dateParameter

There are cases when this query returns NULL, because there are no rows matching the criteria, in those situations I want the query to return the same value, except when Foo.Baz = 0.

I could do this with two subqueries, but it seems to be a hack. Isn't there a better way?

SELECT
    COALESCE( Attempt1.BarData, Attempt2.BarData )
FROM
    (
        SELECT
            MAX( Foo.BarData ) As BarData
        FROM
            Foo
        WHERE
            Foo.Baz = 2
            AND
            Foo.BarData < @dateParameter
    ) As Attempt1
    OUTER JOIN
    (
        SELECT
            MAX( Foo.BarData ) As BarData
        FROM
            Foo
        WHERE
            Foo.Baz = 0
            AND
            Foo.BarData < @dateParameter
    ) As Attempt2 ON 1 = 1

Answer 1

Just select both lines and limit the result to 1 line, so the preferred line was the first

SELECT TOP 1
  MAX( Foo.BarData )
FROM
  Foo
WHERE
  ((Foo.Baz = 2) OR
  (Foo.Baz = 0))
AND
  Foo.BarData < @dateParameter
GROUP BY Foo.Baz
ORDER BY Foo.Baz DESC

Answer 2

MAX Foo.Baz , CASE

SELECT COALESCE(MAX(CASE WHEN Foo.Baz = 2 THEN Foo.BarDate ELSE NULL END)
              , MAX(CASE WHEN Foo.Baz = 0 THEN Foo.BarDate ELSE NULL END)
               )
FROM   Foo
WHERE  Foo.Baz IN (0,2)
  AND  Foo.BarDate < @dateParameter

Answer 3

SELECT COALESCE(    
    (SELECT MAX(Foo.BarData) FROM Foo WHERE Foo.Baz = 2 AND Foo.BarData < @dateParameter),
    (SELECT MAX(Foo.BarData) FROM Foo WHERE Foo.Baz = 0 AND Foo.BarData < @dateParameter)
)

, . SQL Server FROM. Oracle DUAL. , .

OP , . , . , sql-esque, .

, Foo.Baz = 2, , :

SELECT COALESCE(
    MAX(Foo.BarData),
    (SELECT MAX(Foo.BarData) FROM Foo WHERE Foo.Baz = 0 AND Foo.BarData < @dateParameter)
)
FROM Foo
WHERE Foo.Baz = 2 AND Foo.BarData < @dateParameter)

Answer 4

, IF..ELSE, .

IF EXISTS (SELECT 1 FROM Foo WHERE Foo.Baz = 2)
BEGIN
SELECT  MAX( Foo.BarData)
FROM    Foo
WHERE   Foo.Baz = 2
AND     Foo.BarData < @dateParameter
END
ELSE
BEGIN
SELECT  MAX( Foo.BarData)
FROM    Foo
WHERE   Foo.Baz = 0
AND     Foo.BarData < @dateParameter
END

.