Create a custom column in SELECT statement for SOR

Question

Here's my query:

SELECT name, 1 as rank
FROM names
WHERE name LIKE '%abc%'
UNION
SELECT name, 2 as rank
FROM names
WHERE name LIKE '%xyz%'
ORDER BY rank

The problem is duplicate rows won't be combined by UNION since they would differ in the rank column. Now, how would I hide the custom column rank but still use it in ORDER BY clause? Thank you.

EDIT:

I'm actually trying to sort by relevance, eg., whole words && exact phrase first, whole words && non-exact second, non-whole && non-exact third, and so on.

Answer 1

Forget the union and subqueries. Just do:

select name
from names
where name like '%abc%' or name like '%xyz%'
order by (case when name like '%abc%' then 1
               when name like '%xyz%' then 2
               else 3
          end);

Note: If you are concerned about duplicates, you can use select distinct name.

Answer 2

Try not combining with union, but use a case statement instead. This avoids your duplicate issue.

Select name from
(Select name , 
Case when name like '%abc%' then 1 else 2 end as rank
 From names
Where name like '%and%' or name like '%xyz%' 
Order by rank)
Group By name;

It looks like you want to select all names with 'abc' from names and then select names with 'xyz'.

With this query anything with 'abc' is ranked 1 and anything with 'xyz' (but not 'abc') is ranked 2.

In our where clause we only select names with 'abc' or 'xyz'. The case applies 1 to 'abc' and all other rows ('xyz' only) get 2. Ordering should work now with no duplication