How to select the top n from a union of two queries where the resulting order needs to be ranked by individual query?

Let's say I have a table with usernames:

Id  |  Name
-----------
1   |  Bobby
20  |  Bob
90  |  Bob
100 |开发者_运维技巧  Joe-Bob
630 |  Bobberino
820 |  Bob Junior

I want to return a list of n matches on name for 'Bob' where the resulting set first contains exact matches followed by similar matches.

I thought something like this might work

SELECT TOP 4 a.* FROM
(
    SELECT * from Usernames WHERE Name = 'Bob'
    UNION
    SELECT * from Usernames WHERE Name LIKE '%Bob%'
) AS a

but there are two problems:

1. It's an inefficient query since the sub-select could return many rows (looking at the execution plan shows a join happening before top)
2. (Almost) more importantly, the exact match(es) will not appear first in the results since the resulting set appears to be ordered by primary key.

I am looking for a query that will return (for TOP 4)

Id | Name
---------
20 | Bob
90 | Bob

(and then 2 results from the LIKE query, e.g. 1 Bobby and 100 Joe-Bob)

Is this possible in a single query?

You could use a case to place the exact matches on top:

select  top 4 *
from    Usernames
where   Name like '%Bob%'
order by
        case when Name = 'Bob' then 1 else 2 end

Or, if you're worried about performance and have an index on (Name):

select  top 4 *
from    (
        select  1 as SortOrder
        ,       *
        from    Usernames
        where   Name = 'Bob'
        union all
        select  2
        ,       *
        from    Usernames
        where   Name like  '%Bob%'
                and Name <> 'Bob'
                and 4 >
                (
                select  count(*)
                from    Usernames
                where   Name = 'Bob'
                )
        ) as SubqueryAlias
order by
        SortOrder

A slight modification to your original query should solve this. You could add in an additional UNION that matches WHERE Name LIKE 'Bob%' and give this priority 2, changing the '%Bob' priority to 3 and you'd get an even better search IMHO.

SELECT TOP 4 a.* FROM
(
    SELECT *, 1 AS Priority from Usernames WHERE Name = 'Bob'
    UNION
    SELECT *, 2 from Usernames WHERE Name LIKE '%Bob%'
) AS a
ORDER BY Priority ASC

This might do what you want with better performance.

SELECT TOP 4 a.* FROM
(
    SELECT TOP 4 *, 1 AS Sort from Usernames WHERE Name = 'Bob'
    UNION ALL
    SELECT TOP 4 *, 2 AS Sort from Usernames WHERE Name LIKE '%Bob%' and Name <> 'Bob'
) AS a
ORDER BY Sort

This works for me:

SELECT TOP 4 * FROM (
SELECT 1 as Rank , I, name  FROM Foo  WHERE Name = 'Bob' 
UNION ALL
SELECT 2 as Rank,i,name  FROM Foo  WHERE Name LIKE '%Bob%' 
) as Q1
ORDER BY Q1.Rank, Q1.I

SET ROWCOUNT 4

SELECT * from Usernames WHERE Name = 'Bob'
UNION
SELECT * from Usernames WHERE Name LIKE '%Bob%'

SET ROWCOUNt 0

The answer from Will A got me over the line, but I'd like to add a quick note, that if you're trying to do the same thing and incorporate "FOR XML PATH", you need to write it slightly differently.

I was specifying XML attributes and so had things like :

SELECT Field_1 as [@attr_1]

What you have to do is remove the "@" symbol in the sub queries and then add them back in with the outer query. Like this:

SELECT top 1 a.SupervisorName as [@SupervisorName]
FROM
(
    SELECT (FirstNames + ' ' + LastName) AS [SupervisorName],1 as OrderingVal
    FROM ExamSupervisor SupervisorTable1

    UNION ALL

    SELECT (FirstNames + ' ' + LastName) AS [SupervisorName],2 as OrderingVal
    FROM ExamSupervisor SupervisorTable2

) as a
ORDER BY a.OrderingVal ASC
FOR XML PATH('Supervisor')

This is a cut-down version of my final query, so it doesn't really make sense, but you should get the idea.