CODEWITHSUNDEEP
mysqldistinctunion
DylanVB
DylanVB

Reputation: 307

MySQL - UNION DISTINCT with extra column

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!

Upvotes: 1

Views: 1216

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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

Upvotes: 2

Abhilekh Singh
Abhilekh Singh

Reputation: 2943

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;

Upvotes: 1

Related Questions