agahi agahi - 1 year ago 58
SQL Question

How to order by best match

Consider this


SELECT * FROM course WHERE description LIKE '%university%' OR description LIKE '%history%'

Obviously I would want to see the records where both conditions are true first ie records that contain both
then records that contain only one of these.

This is especially important when I want to put
Any idea how to do that without looping through all the results?

Answer Source

It's simple and you can use simple order clause to achieve desired result:

SELECT * FROM course
WHERE description LIKE '%university%' OR description LIKE '%history%'
ORDER BY IF(description LIKE '%university%' and description LIKE '%history%', 0, 1)
LIMIT 0, 20

even you can retrieve entries that match only university or history keywords prior to other.


For more complex search it's better to implement MATCH AGAINST functionality of MySQL, see this: otherwise LIKE clause does not allow IN statement. Also you can use REGEXP like this: MySQL match() against() - order by relevance and column?

For some other example you can use REGEXP in WHERE clause like this:

WHERE description REGEXP 'university|history|literature'

but this case you have to still build same query for ORDER clause like first example I've given.

Anyway if you build this string dynamically it would not be hard, but ORDERing would go complex in that cases. Only MATCH AGAINST would be the ideal solution for your problem.