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
Comment by Fred — 30.09.2008 @ 10:44
Comment by AquilaX — 30.09.2008 @ 10:55
Comment by Eric Marden — 31.07.2009 @ 13:36
Comment by Sunny — 17.11.2010 @ 13:21
Comment by AquilaX — 17.11.2010 @ 13:33
Comment by Sunny — 17.11.2010 @ 14:17
Comment by Sunny — 17.11.2010 @ 14:24
Comment by AquilaX — 17.11.2010 @ 15:02
Comment by rye coder — 09.01.2011 @ 18:03
Comment by Improve MySQL search by using Union statement | Sunfine Data — 13.08.2012 @ 05:15