Skip to content

MySQL UNION DISTINCT with result priority

I had to modify the search algorithm today, the idea was to fetch the exact matches first then the split words matches. The status quo was:

 
(SELECT product_id FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id FROM products WHERE a=2 OR a=1)
 

I wanted to be sure that the result from the first sub-query are before the second one. The first thing that kame to my mind was:

 
(SELECT product_id, 1 as priority FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id, 2 as priority FROM products WHERE a=2 OR a=1)
ORDER BY priority
 

but unfortunately this breaks the UNION DISTINCT clause and the results are duplicated.

I came out with the following solution:

 
SELECT MIN(priority) AS priority, product_id
FROM (
  (SELECT product_id, 5 AS priority FROM products WHERE a=1)
    UNION
  (SELECT product_id, 6 AS priority FROM products WHERE a=1 OR a=2)
) AS t
GROUP BY (product_id)
ORDER BY priority
 
  • http://www.fredericburdet.com Fred

    Hello,

    I would like to use your solution for UNION with result priority.
    I am a beginner in SQL and I can’t understand your code:
    what does MIN mean?
    what are the 5 and 6 in the SELECT?
    what is the AS t?

    Thanks in advance to help me!
    Greetings!

  • http://blog.horemag.net AquilaX

    Hi,
    MIN is aggregation function, which gets the minimum value of the column, in this case it chooses the priority;

    5 and 6 are the priorities you can place whatever numbers You want, the smaller, the greater is the priority (because of the MIN function);

    t is placed there because alias is expected by MySQL, you can replace it with whatever you want;

    Hope this was helpful.

  • http://xentek.net/ Eric Marden

    Thanks for the post. You just helped me solve a problem I’ve been chasing for days. If you’re ever in my neck of the woods, I owe you a beverage of your choice.

  • Sunny

    Nice post!

    But what if I need to select * instead of one column ????

  • http://blog.horemag.net AquilaX

    Just use * instead of the column it should work.

  • Sunny

    Not working

  • Sunny

    Actually, I tested it:
    By default first query in union has top priority and so on.

    The problem I am facing now is:
    I want 12 results on every page.
    So, Is there a method by which I can give Limit to whole query not just in every union query.

  • http://blog.horemag.net AquilaX

    Yes there is just enclose everything in one select:

    SELECT(
    SELECT… whatever
    ) as outer LIMIT 10;

  • rye coder

    wow, great solution. This saved me a lot of time. thanks alot