MySQL - UNION DISTINCT with extra column

Question

I am trying to make a MySQL query for a "search" in one of my applications. This is what I currently have:

(SELECT id, title, 1 AS `order` FROM tasks WHERE title LIKE '%Task%Test%') UNION DISTINCT (SELECT id, title, 2 AS `order` FROM tasks WHERE title LIKE '%Task%' AND title LIKE '%Test%') ORDER BY `order` ASC;

The second query of the UNION kind of acts as a fallback for the first one, since the first query expects the words to be in the same order. (The amount of words is dynamic, depending on the search query.)

In the results, I want to show the results of the first query first, because those are more precise. To do this, I tried to add an extra column to the results: order.

My problem is that DISTINCT only works for rows that are entirely identical, which is not the case for my rows, because of the orders column.

Example

Table:

id title 1 'Test Task' 2 'The Second Test Task' 3 'A Third Task For Testing' 4 'A Fourth Test'

Result:

id title order 3 'A Third Task For Testing' 1 1 'Test Task' 2 2 'The Second Test Task' 2 3 'A Third Task For Testing' 2

What I want:

id title order 3 'A Third Task For Testing' 1 1 'Test Task' 2 2 'The Second Test Task' 2

Anyone who can help me?

Thanks in advance!

why second conditions isnt WHERE title LIKE '%Test%Task%' first condtion already got the one where Task is first. So the remaining if also have both mean Test is first — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 1, 2017 at 13:58
Because the amount of words to search for can change (it can also be '%Test%Task%With%More%Words%', for example). Then I have to check for all possible orders, which has a big effect on the execution time. This approach should be much faster. — DylanVB
– DylanVB, Commented Dec 1, 2017 at 14:05

Tim Biegeleisen · Accepted Answer · 2017-12-01 14:03:46Z

2

I think you can get the same result from a single query without using a union:

SELECT id, title, CASE WHEN title LIKE '%Task%Test%' THEN 1 WHEN title LIKE '%Task%' AND title LIKE '%Test%' THEN 2 ELSE 3 END AS label FROM tasks WHERE title LIKE '%Task%Test%' OR (title LIKE '%Task%' AND title LIKE '%Test%') ORDER BY label;

Demo

edited Dec 1, 2017 at 14:03

answered Dec 1, 2017 at 13:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Juan Carlos Oropeza Over a year ago

With the WHERE You dont need the ELSE

Tim Biegeleisen Over a year ago

@JuanCarlosOropeza I know...I left it in there just as a marker.

DylanVB Over a year ago

This is probably faster than using the UNION?

Juan Carlos Oropeza Over a year ago

Maybe, but still will be slower. This LIKE condition cant use index. If you want performance go with TEXT SEARCH

Raymond Nijland Over a year ago

As a addition to @JuanCarlosOropeza 's comment LIKE 'a%' can and will use a index if anny but LIKE '%a%' can't use a index

Abhilekh Singh · Accepted Answer · 2017-12-01 13:49:29Z

You can use group by:

SELECT id, title, `order` FROM ( (SELECT id, title, 1 as `order` FROM tasks WHERE title LIKE '%Task%Test%') UNION DISTINCT (SELECT id, title, 2 as `order` FROM tasks WHERE title LIKE '%Task%' AND title LIKE '%Test%') ) tb GROUP BY id ORDER BY `order` ASC;

Collectives™ on Stack Overflow

MySQL - UNION DISTINCT with extra column

2 Answers 2

Demo

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related