How to correctly compose an SQL query using order by case?

Question

MySQL

Hello. It is required that the output result be sorted by the maximum occurrence of words in the product title. I'm trying this option:

SELECT * FROM products 
order by case 
    when title LIKE "%Спиннинг%" AND title LIKE "%Карбоновый%" AND title LIKE "%Белый%" AND active = 1 then 1 
    when title LIKE "%Спиннинг%" AND title LIKE "%Карбоновый%" AND active = 1 then 2 
    when title LIKE "%Спиннинг%" AND active = 1 then 3
    else 4
end

And it seems to work perfectly, first white carbon spinning rods are displayed, then just carbon spinning rods and then all the products where there is a "spinning" heading.

But after that, all other products are displayed, where even "Spinning" is not in the title. How to cut off non-search items in the current query design?

Answer 1

Add condition WHERE title LIKE '%Spinning%'