CODEWITHSUNDEEP
mysqlsql-order-by
Spencer Carnage
Spencer Carnage

Reputation: 2066

MySQL Order By not using ASC or DESC

I have a column using IDs that I want to sort by. It has ID values from 1 to 3. However, instead of just using ASC of DESC, I want to do a custom sort by 2, 3, 1. How do I make this happen?

Upvotes: 3

Views: 897

Answers (3)

edwin
edwin

Reputation: 2821

I think the easiest way is to do it like this:

SELECT * FROM `mytable` ORDER BY FIND_IN_SET(id, '2,3,1')

Upvotes: 6

Heinzi
Heinzi

Reputation: 172220

Untested:

SELECT ..., IF(ID=2, 1, IF(ID=3, 2, 3)) AS orderByValue
  FROM ...
 ORDER BY orderByValue

It uses the IF function to convert ID values:

 ID     orderByValue
  2        1
  3        2
else       3

Upvotes: 0

eKek0
eKek0

Reputation: 23289

You could add a virtual column with values would be

MOD(ID, 3)

and order your query ascending by it. For example:

SELECT somecolumn, MOD(ID, 3) AS ordered_id FROM my_table ORDER BY ordered_id

Upvotes: 1

Related Questions