SQL Union with "Ranks"

Question

I am writing a query to search a table for a specific user, this will provide an exact match and potential matches.

Essentially, the users email address is unique, therefore I do not need to return more than this row, however beyond this I need to return all the potential users with a 'rank' of how likely a match they are.

I want the query to be as efficient as possible, and to this point have avoided a cursor as I felt it not to be neccessary. Here is an example of my current query.

    SELECT 
            tt.userId,
            tt.UserName,
            ad.*,
            1 AS 'Rank'
        FROM
            Users.User tt
        LEFT JOIN
            General.[Address] ad ON tt.AddressId = ad.AddressId
        WHERE
            tt.EmailAddress = @EmailAddress
    UNION

        SELECT 
            tt.userId,
            tt.UserName,
            ad.*,
            2 AS 'Rank'
        FROM
            Users.User tt
        LEFT JOIN
            General.[Address] ad ON tt.AddressId = ad.AddressId
        WHERE
            tt.LastName = @LastName
            AND tt.BirthDate = @DOB

Obviously the primary flaw here is, if the email address matched, the same user is going to be appearing in the second query also and so on. Also as I need to rank these searches the union isn't going to match the rows as a duplicate so I will get the same user back several times.

Any advice is greatly appreciated.

Answer 1

You don't need union at all:

SELECT tt.userId, tt.UserName, ad.*,
       (CASE WHEN tt.EmailAddress = @EmailAddress THEN 1
             WHEN tt.LastName = @LastName AND tt.BirthDate = @DOB THEN 2
        END) as [Rank]
FROM Users.User tt LEFT JOIN
     General.[Address] ad
     ON tt.AddressId = ad.AddressId
WHERE (tt.EmailAddress = @EmailAddress) OR
      (tt.LastName = @LastName AND tt.BirthDate = @DOB);

Obviously, it is easy to add additional conditions, such as a new ranking when all three columns match.