sql - Mysql: Order by like?

Question

assume that we are performing search using keywords: keyword1, keyword2, keyword3

there are records in database with column "name":

1: John Doe
2: Samuel Doe
3: John Smith
4: Anna Smith

now Query:

SELECT * FROM users WHERE (name LIKE "%John%" OR name LIKE "%Doe%")

it will select records: 1,2,3 (in this order) but i want to order it by keyword in example keyword1=John, keyword2=Doe so it should be listed by keywords: 1,3,2 (because i want to perform search for "Doe" after searching for "John")

I was thinking about SELECT DISTINCT FROM (...... UNION .....) but it will be much easier to order it somehow in another way (real query is really long)

are there any tricks to create such order?

Answer 1

order by case 
    when name LIKE "%John%" then 1 
    when name LIKE "%Doe%"  then 2 
    else 3 
end