How to select the top n from a union of two queries where the resulting order needs to be ranked by individual query?
- by Jedidja
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:
It's an inefficient query since the sub-select could return many rows (looking at the execution plan shows a join happening before top)
(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?
