SQL Server query inefficient for table with high I/O operations

Question

I'm trying to write an sql script that returns an item from a list, if that item can be found in the list, if not, it returns the most recent item added to the list. I came up with a solution using count and an if-else statement. However my table has very frequent I/O operations and I think this solution is inefficient. Does anyone have a away to optimize this solution or a better approach.

here is my solution:

DECLARE @result_set INT
SET @result_set = (
    SELECT COUNT(*) FROM
    ( SELECT *
        FROM notification p 
        WHERE p.code = @code 
            AND p.reference = @reference 
            AND p.response ='00'
    ) x
)

IF(@result_set > 0)
    BEGIN
        SELECT * 
        FROM notification p 
        WHERE p.code = @code 
            AND p.reference = @reference 
            AND p.response ='00'
    END
ELSE
    BEGIN
        SELECT
        TOP 1  p.*
        FROM notification p (nolock) 
        WHERE p.code = @code 
            AND p.reference = @reference
        ORDER BY p.id DESC
    END

I also think there should be a way around repeating this select statement:

SELECT *
    FROM notification p 
    WHERE p.code = @code 
        AND p.reference = @reference 
        AND p.response ='00'

I'm just not proficient enough in SQL to figure it out.

Answer 1

You can do something like this:

SELECT TOP (1) n.*
FROM notification n 
WHERE p.code = @code  AND p.reference = @reference 
ORDER BY (CASE WHEN p.response ='00' THEN 1 ELSE 2 END), id DESC;

This will return the row with response of '00' first and then any other row. I would expect another column i the ORDER BY to handle recency, but your sample code doesn't provide any clue on what this might be.