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

Question

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?

Solution 1

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

Solution 2

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

Solution 3

SET ROWCOUNT 4

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

SET ROWCOUNt 0

Solution 4

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

Solution 5

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

View more solutions

Comments

[Nathan Tregillus]

I like your solution. very slick!

[SilverbackNet]

Thank you! I don't care if it's a dirty hack, it works perfectly <3