CODEWITHSUNDEEP
phpmysqlsql
Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4028

mysql: order the result by column name

$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.

Upvotes: 6

Views: 1834

Answers (6)

devpro
devpro

Reputation: 16117

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.

Upvotes: 2

Pupil
Pupil

Reputation: 23948

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.

enter image description here

Upvotes: 1

Monty Khanna
Monty Khanna

Reputation: 1120

Try this Query : 

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

Upvotes: 0

user3040610
user3040610

Reputation: 760

Try this ,

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

Upvotes: 1

Umair Hamid
Umair Hamid

Reputation: 3557

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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)

Upvotes: 9

Related Questions