How to define a custom ORDER BY order in mySQL
MysqlMysql Problem Overview
In MySQL how do I define a custom sorting order.
To try to explain what I want consider this table:
ID Language Text
0 ENU a
0 JPN b
0 DAN c
1 ENU d
1 JPN e
1 DAN f
2 etc...
here I want to return all rows sorted by Language and ascending ID so that Language = ENU comes first, then JPN and lastly DAN.
The result should be: a,d,b,e,c,f etc.
Is this even possible?
Mysql Solutions
Solution 1 - Mysql
MySQL has a handy function called FIELD()
which is excellent for tasks like this.
ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID
Note however, that
-
It makes your SQL less portable, as other DBMSs might not have such function
-
When your list of languages (or other values to sort by) gets much longer, it's better to have a separate table with sortorder column for them, and join it to your queries for ordering.
Solution 2 - Mysql
If those are the only three values, then you can use a CASE
expression:
ORDER BY `ID`,
CASE `Language`
WHEN 'ENU' THEN 1
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 3
END
(If there could be other values, then you may want to add some extra logic to keep the ordering consistent; for example, you might add ELSE 4
to that CASE
expression, and then order by Language
itself as the third ordering criterion:
ORDER BY `ID`,
CASE `Language`
WHEN 'ENU' THEN 1
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 3
ELSE 4
END,
`Language`
)
Solution 3 - Mysql
You have a couple of options offhand, the first is to change Language to be ENUM (assuming this is possible, and you only expect a few variations)
If you specify it as ENUM('ENU','JPN','DAN')
then ORDER Language ASC
will order in the order you specify.
The second will involve a case somewhere, i.e.
SELECT * FROM table
ORDER BY CASE Language
WHEN 'ENU' THEN 3
WHEN 'JPN' THEN 2
WHEN 'DAN' THEN 1
ELSE 0
END DESC, ID ASC
Performance-wise the ENUM method will return faster results, but be more hassle if you need to add more languages. A third option would be to add a normalisation table for the Languages however that may be overkill in this instance.
Solution 4 - Mysql
For Yii2 framework we can achieve by following way
Project::find()
->orderBy([
new Expression('FIELD(pid_is_t_m,2,0,1)'),
'task_last_work'=> SORT_ASC
])->all();