How to order by best match

Question

Consider this OR query:

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 'university' and 'history' then records that contain only one of these.

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

score 0 · Accepted Answer · edited May 23 '17 at 12:14

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.

UPDATE:

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.

Thanks George. There is still one problem. In this example I am only looking for 2 conditions. What if let's say I have 5 conditions? — agahi, Jul 11 '16 at 12:53
Hello there may be some solutions, best one would be if you could apply full text search see: http://stackoverflow.com/questions/6259647/mysql-match-against-order-by-relevance-and-column — George Garchagudashvili, Jul 11 '16 at 13:00

score 0 · Answer 2 · answered Jul 11 '16 at 15:49

Try something like that:


select 
case 
when description like '%university%' and description like '%history%' then 2
when description like '%university%' then 1 
when description like '%history%' then 1 else 0 end as rank

Then just order by rank at the end of your query.

That will allow you to rank by more than two descriptions as long as you keep adding then.

How to order by best match

2 Answers2