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?