AquilaX's development blog

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

¶ Posted 31 Jul 2008 † AquilaX§ MySql ‡ Comments (10)

Comments

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!

Comment by Fred — 30.09.2008 @ 10:44

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.

Comment by AquilaX — 30.09.2008 @ 10:55

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.

Comment by Eric Marden — 31.07.2009 @ 13:36

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

Comment by Sunny — 17.11.2010 @ 13:21

Just use * instead of the column it should work.

Comment by AquilaX — 17.11.2010 @ 13:33

Not working

Comment by Sunny — 17.11.2010 @ 14:17

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.

Comment by Sunny — 17.11.2010 @ 14:24

Yes there is just enclose everything in one select: SELECT( SELECT... whatever ) as outer LIMIT 10;

Comment by AquilaX — 17.11.2010 @ 15:02

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

Comment by rye coder — 09.01.2011 @ 18:03

[...] (From http://dev.horemag.net/2008/07/31/mysql-union-distinct-with-result-priority/) [...]

Comment by Improve MySQL search by using Union statement | Sunfine Data — 13.08.2012 @ 05:15