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 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.
GROUP BY before? For example, the first value i want, appears at the end?
GROUP BY in a subquery, and order it in the outer query
If those are the only three values, then you can use a CASE expression:
ORDER BY CASE `Language` WHEN 'ENU' THEN 1 WHEN 'JPN' THEN 2 WHEN 'DAN' THEN 3 END, `ID` (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 an additional ordering criterion:
ORDER BY CASE `Language` WHEN 'ENU' THEN 1 WHEN 'JPN' THEN 2 WHEN 'DAN' THEN 3 ELSE 4 END, `Language`, `ID` )
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.
ENUM('ENU','JPN','DAN')?
END DESC, be END CASE DESC, ?CASE need END CASE, it's dependant on context. CASE within PROCEDURE require END CASE ( dev.mysql.com/doc/refman/5.5/en/case.html ) however CASE within SELECT does not require END CASE, simply END (dev.mysql.com/doc/refman/5.7/en/… ) - in this context it's a control flow function.For Yii2 framework we can achieve by following way
use yii\db\Expression; Project::find() ->orderBy([ new Expression('FIELD(pid_is_t_m,2,0,1)'), 'task_last_work'=> SORT_ASC ])->all(); 
use yii\db\Expression;