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.

Answer 3

You can use ORDER BY for multiple columns like:

SELECT * FROM tablename ORDER BY category DESC, title DESC, description DESC

I have tried it and it worked.

Answer 4

Try this ,

SELECT * FROM table where 'car' IN (category, title, description)  ORDER BY category DESC, title DESC, description DESC

Answer 5

You can have multiple ORDER BY clause in your query.

    select *from table where category='car' or title='car' or description='car' ORDER BY category DESC, title DESC, description DESC

See this answer for reference. mysql query order by multiple items

Answer 6

Try this Query :

select * from table 
where category='car' or title='car' or description='car' 
order by 1 desc, 2 desc, 3 desc