How to define a custom ORDER BY order in mySQL

Question

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?

score 350 · Accepted Answer · edited Nov 04 '19 at 10:56

350

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.

edited Nov 04 '19 at 10:56

SuperShoot

9,880
2
38
55

answered Feb 21 '12 at 13:49

Mchl

61,444
9
118
120

4

Thanks this is perfectly for my situation where I just have to order by two values (a primary language, for example JPN, and a fallback language, for example ENU). – Muleskinner Feb 21 '12 at 14:11
6

Man you just saved me a rewrite in magento :) – Erik Simonic Feb 06 '15 at 15:18
1

What if you have a `GROUP BY` before? For example, the first value i want, appears at the end? – Pathros Mar 25 '15 at 18:51
Put the query with `GROUP BY` in a subquery, and order it in the outer query – Mchl Mar 25 '15 at 20:55
Nice. I was writing a case order by clause, but field() is much more compact, and easier to reorder. – Geoffrey Wiseman Jun 02 '16 at 13:21
Works perfectly as of november 21, 2022! – Nicholas Hansen-Feruch Nov 21 '22 at 21:14
Is there a way to make this work so that something not on the list is at the end, rather than the beginning? If I throw the example query 'RUS', it will put that *before* any of the ones specifically listed. – Stephen R Jun 22 '23 at 15:56

score 72 · Answer 2 · edited Nov 04 '19 at 10:59

72

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`

)

edited Nov 04 '19 at 10:59

SuperShoot

9,880
2
38
55

answered Feb 21 '12 at 13:43

ruakh

175,680
26
273
307

1

And if there are many Language values you could have a separate table storing each Language plus a sort order column and link to that – kaj Feb 21 '12 at 13:45
2

This will order by ID first, resulting in a,b,c,d,e,f – piotrm Feb 21 '12 at 13:56
Thanks this works perfectly - as did Mchl answer which I accepted as it looks simpler – Muleskinner Feb 21 '12 at 14:02

Simon at The Access Group · Answer 3 · 2016-12-20T09:57:31.897

25

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.

edited Dec 20 '16 at 09:57

answered Feb 21 '12 at 13:46

Simon at The Access Group

6,932
22
45

Where exactly do you type `ENUM('ENU','JPN','DAN')`? – Pathros Mar 25 '15 at 18:52
1

@pathros in the table definition, you specify it as an ENUM instead of VARCHAR etc. Internally MySQL stores the ENUM options in a specific order and indexes them, and so when ordering by an ENUM column specifically it will use that internal index instead of the string values ( unless CAST() is used to make it back to a VARCHAR) – Simon at The Access Group Mar 26 '15 at 09:36
Shouldn't `END DESC,` be `END CASE DESC,` ? – Istiaque Ahmed Nov 10 '17 at 14:11
Nope. Not all `CASE` need `END CASE`, it's dependant on context. `CASE` within PROCEDURE require `END CASE` ( https://dev.mysql.com/doc/refman/5.5/en/case.html ) however `CASE` within SELECT does not require `END CASE`, simply `END` (https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case ) - in this context it's a control flow function. – Simon at The Access Group Nov 10 '17 at 18:33

Prahlad · Answer 4 · 2023-07-07T10:31:24.663

2

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();

edited Jul 07 '23 at 10:31

answered Apr 10 '20 at 02:46

Prahlad

716
8
17

1

Your file should also have this line: `use yii\db\Expression;` – Quique Jul 07 '23 at 10:12

How to define a custom ORDER BY order in mySQL

4 Answers4

Linked

Related