How to select a row depending on if exist case in SQL Server?

Question

I want to select an item with certain brand and if the brand is not included in the table select the item with brand name 'all'. I have the table1 like this :

Discount |  Brand
    20   |   ford
    10   |   all

And I have the query parameter named @Brand_name. All I want is returning the rate of the brand if exist in table. Otherwise return the rate with brand name 'all'. What is the correct query for doing that. Thanks for any help.

Answer 1

Try this:

SELECT TOP 1 Discount
FROM mytable
WHERE Brand = @Brand_name OR Brand = 'all'
ORDER BY CASE 
            WHEN Brand = 'all' THEN 1
            ELSE 0 
         END

The query returns always one record since record with Brand = 'all' is selected and TOP 1 is used in the SELECT clause.

In case you have more the one 'Brand' records and you want all of them returned, then you can use the following query:

;WITH CTE AS (
    SELECT Discount,
           RANK() OVER (ORDER BY CASE 
                                    WHEN Brand = 'all' THEN 1
                                    ELSE 0 
                                  END) AS rnk
    FROM mytable
    WHERE Brand = @Brand_name OR Brand = 'all'
)
SELECT Discount
FROM CTE
WHERE rnk = 1

Demo here