MYSQL: How can I implement a order by 'Best match' in a query containing `UNION ALL`

Question

I have a products database, and this is how I'm running a query to show results for the keywords 'chair':

SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE  `product_name`  LIKE '%chair%'
AND `products`.`active` =  1

 UNION ALL

SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE  `category_id` IN (128,135,136,138)
AND `products`.`active` =  1

I saw on this thread: MySQL order by "best match" how to order by 'best match' given only a %like% constraint.

How can I implement something similar on my particular query?

You haven't specified what you mean by "best match". Until you can explain the exact process for ranking matches nobody can possibly help you. — Jim Garrison, Feb 07 '14 at 01:38

score 1 · Accepted Answer · answered Feb 07 '14 at 04:30

If I don't misunderstand what you are after, you can try to put ordering statement at the end. And put brackets around each select statement to prevent MySQL from wrongly applying ORDER BY to result of the second SELECT statement instead of entire UNION ALL result :

(SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE  `product_name`  LIKE '%chair%'
AND `products`.`active` =  1)

 UNION ALL

(SELECT `products`.product_id, products.product_name
FROM (`products`)
WHERE  `category_id` IN (128,135,136,138)
AND `products`.`active` =  1)

ORDER BY
  CASE
    WHEN `product_name` LIKE 'chair%' THEN 1
    WHEN `product_name` LIKE '%chair' THEN 3
    ELSE 2
  END

sorry for not making this answer looks complicated enough :D — har07, Feb 07 '14 at 06:21

MYSQL: How can I implement a order by 'Best match' in a query containing `UNION ALL`

1 Answers1