How to define a custom ORDER BY order in mySQL

mysql
Muleskinner picture Muleskinner · Feb 21, 2012 · Viewed 91.1k times · Source

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?

Answer

Mchl picture Mchl · Feb 21, 2012

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

  1. It makes your SQL less portable, as other DBMSs might not have such function

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

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More