How can I sort the result of a query based on a single column?

Question

Here is my request:

SELECT name, usage_guidance, total_used_num FROM tags WHERE ( name LIKE CONCAT('%', ?, '%') OR usage_guidance LIKE CONCAT(?, '%') ) AND name NOT IN ($in) LIMIT 6 

Now I want to arrange the result with a name column. I mean, I want to first achieve consistent results due to the name LIKE CONCAT('%', ?, '%') Condition, and then there should be other results after them. How can i do this?

Answer 1

You must add where clauses to the order by clause:

 order by (name like CONCAT('%', ?, '%')) desc, (usage_guidance LIKE CONCAT(?, '%')) desc 

MySQL treats logical expressions in a numeric context as numbers, with "1" for true and "0" for false. Therefore, desc order for sorts.

Please note that the second condition on usage_guidance not necessary to answer the question.

Answer 2

If I understand your question correctly, you want results that first match name LIKE %SOMETHING% .

This can be done by setting an additional field in the IF() statement with the same condition as the WHERE for name , and sorting by this field:

 SELECT name, usage_guidance, total_used_num, name LIKE CONCAT('%', ? , '%') as sort_field FROM tags WHERE ( name LIKE CONCAT('%', ?, '%') OR usage_guidance LIKE CONCAT(?, '%') ) AND name NOT IN ($in) ORDER BY sort_field DESC LIMIT 6 

Edit: I realized you don't need an IF() operator at all