How to select a row depending on whether a case exists in SQL Server?

Question

I want to select an item with a specific brand, and if the brand is not included in the table, select the item with the brand name "all". I have table1 as follows:

Discount |  Brand
    20   |   ford
    10   |   all

And I have a query parameter named @Brand_name. All I want is a refund of the brand, if it exists in the table. Otherwise, return the bid with the "all" trademark. What is the correct request for this. Thanks for any help.

Answer 1

Try the following:

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 always returns one record, since the record c is Brand = 'all'selected, but is TOP 1used in the sentence SELECT.

'Brand' , , :

;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

Answer 2

:

IF EXISTS (SELECT 1 FROM table1 WHERE Brand = @Brand_name)
BEGIN
    SELECT Discount FROM table1 WHERE Brand = @Brand_name
END
ELSE
BEGIN
    SELECT Discount FROM table1 WHERE Brand = 'all'
END

Answer 3

Adding this as another solution, but not sure though if this is better:

SELECT TOP 1 Discount FROM (
    SELECT Discount FROM table1 WHERE BrandName=@Brand_name
    UNION SELECT Discount FROM table1 WHERE BrandName='all'
) discount

Answer 4

I would choose a different approach, which, I think, could improve performance:

SELECT top 1 discount ,
           brand
FROM
  ( SELECT discount ,
           brand ,
           selector ,
           min(selector) over () [minselect]
   FROM
     ( SELECT discount ,
              brand ,
              1 [selector]
      FROM mytable
      WHERE brand = 'ford'
        UNION ALL
        SELECT discount ,
               brand ,
               2 [selector]
        FROM mytable WHERE brand = 'all' ) r
   GROUP BY discount ,
            brand ,
            selector ) r
WHERE selector = minselect
ORDER BY discount DESC