1

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.

Sid Go
  • 2,041
  • 3
  • 16
  • 29
  • Have you try it `UNION ALL`?? But not sure what you want. Can you show us sample data, current and expected result? Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza May 29 '16 at 02:20

2 Answers2

1

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

EoinS
  • 5,405
  • 1
  • 19
  • 32
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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But how does this work (sorry for my ignorance). Can you elaborate a bit what does it mean when you tell SQLite to ORDER BY 1 or ORDER BY 2, etc.? I thought ORDER BY can be used only for columns. Thank you. – Sid Go May 29 '16 at 03:05
  • @SidGo . . . `ORDER BY` can be used on expressions. Those values are integers that specify the priorities for ordering. – Gordon Linoff May 31 '16 at 00:19