Prioritizing strings in a select statement

Question

I have a select statement where if a string contains any of the words below, it'll select it, but it doesn't select it in order of the words contain. For example, if the string in the database is "three two one", and not "one two three", it'll select it anyway. I want to make it, so that it prioritize in the order of the string first, before trying to get those that's not in that order. Is that possible?

$text1 = "one";
$text2 = "two";
$text3 = "three";

SELECT text,
((text LIKE '%$text1%') + (text LIKE '%$text2%') + (text LIKE '%$text3%')) as `matches` 
FROM tableName 
HAVING `matches` > 0
ORDER BY `matches` DESC, rand() LIMIT 1

More examples: So, if there are 2 strings in the database.

One is

"one two three" 

and the other is

"three two one"

I would like the one with "one two three" to be selected first, before going on to the "three two one" if there aren't any "one two three";

Answer 1

You can specify the priority in the order by:

SELECT text,
       ((text LIKE '%$text1%') + (text LIKE '%$text2%') + (text LIKE '%$text3%')) as `matches` 
FROM tableName 
HAVING `matches` > 0
ORDER BY `matches` DESC, 
         (case when text like '%$text1%$text2%text3%' then 1
               when text like '%$text3%$text2%text1%' then 2
               . . .
          else 999 end),
         rand()
LIMIT 1;