Order mysql result set by number of matches in like clause

Question

The scenario is that i have implemented a search query using like operator:

.. WHERE caption LIKE 'hello%' OR text LIKE '%hello'
      OR caption LIKE 'jame%' OR text LIKE 'jame%'

and the table is something similar to:

id | caption | text
---------------------
1  | Hi      | Hi Jame
2  | Hello   | Hello firend
3  | Jame    | Hello jame

so i expect that result set to be order like this:

id | caption | text
---------------------
3  | Jame    | Hello jame
1  | Hi      | Hi Jame
2  | Hello   | Hello firend

because 3rd row has more matches to WHERE and LIKEclauses.

Is there a way to do that ?

Answer 1

SELECT            *
FROM     ( SELECT *,
                 CASE
                         WHEN caption LIKE 'hello%' OR text LIKE '%hello'
                         THEN 1
                         WHEN caption LIKE 'jame%' OR text LIKE 'jame%'
                         THEN 2                         
                         ELSE 0
                 END AS weight
         FROM    your_table
         )
         q
WHERE    q.weight > 0
ORDER BY q.weight

Answer 2

try this

     WHERE caption LIKE 'hello%' OR text LIKE '%hello'
  OR caption LIKE 'jame' OR text LIKE 'jame'
  ORDER BY caption DESC 

or easier like that

     WHERE caption LIKE 'hello' 
  OR caption LIKE 'jame'
  ORDER BY caption DESC