mysql: order the result by column name

Question

$qry="select * from table where category='car' or title='car' or description='car'";

but I want the output to list the rows by category first and then title and then description.

****Edit: actually I am using a like operator to search**

Example:

 id category title description   
  1    car 
  2    car
  3            car
  4            car
  5            car
  6                    car

is there any way other than using union?
Thanks in advance.

Answer 1

You can do this using ORDER BY with the right keys. In MySQL, you can do:

ORDER BY (category = 'car') DESC,
         (title = 'car') DESC,
         (description = 'car') DESC

MySQL treats boolean expressions as integers in a numeric context, with 0 for false and 1 for true. So the DESC puts the true versions first.

You can also simplify the WHERE clause if you like:

WHERE 'car' IN (category, title, description)

Answer 2

You can get this result by using this statement:

SELECT * FROM mytable 
WHERE (category='car' OR title='car' OR description='car')
ORDER BY category = 'car' DESC,
title = 'car' DESC,
description = 'car' DESC

How it works?

It will set the orders of data in DESC by sequentially as mentioned in query. You can change the sequence as you want.