Select a record and return default value if record doesn't exist

Consider the following set of SQL data in SQL Server:

Model	Discount
Honda	15
Toyota	17
Default	10

We also have a query parameter stored in a variable called @Model_name. We want to query the table using this variable. If a record is found then we want to return it, otherwise we want to return the record with Model = 'Default'.

To get this result we can use this query:

SELECT TOP 1 Discount
FROM mytable
WHERE Model = @Model_name OR Model = 'Default'
ORDER BY CASE 
            WHEN Model = 'Default' THEN 1
            ELSE 0 
         END

The trick here is to use a CASE expression in the ORDER BY clause in order to prioritize records that match @Model_name value over 'Default' record.

The query returns always one record. In case Model field contains duplicates and we want all of them returned, then we can use the following query:

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

This query uses RANK window function in order to prioritize. The trick here is to use a CASE expression in the ORDER BY of OVER clause. This way RANK assigns a value of 1 to all records that match @Model_name value.