Question 191983 in Mysql Question posted in
The official MySQL documentation can be found here.

Custom sorting in MySQL using FIELD, but put unknown/unexpected values at the end

In another question, someone asks how to do custom sorting in MySQL. They give the following example dataset:

ID  Language    Text
0   ENU         a
0   JPN         b
0   DAN         c       
1   ENU         d
1   JPN         e
1   DAN         f
2   etc...

…for which they want results sorted non-alphabetically by Language, in the following order: ENU, JPN, DAN.

The accepted answer says to use the MySQL FIELD command, like so:

ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

My question is this: FIELD returns a numeric value by order, and sorts on that. So in the above, "ENU" is 1, "JPN" is 2, and "DAN" is 3. If there is a value presented that is not explicitly listed in the FIELD function, it returns 0; which means the unspecified values will come first in the sorting. Is there any way to have unexpected values sort after the custom-sorted values? So if I’m sorting with that ORDER, and a record has a language of "RUS", that record would sort last instead of first? If we have both "RUS" and "ESP", it would do the custom sort first, followed by the unexpected values alphabetically?

(I’m aware of CASE/WHEN, but FIELD is so much cleaner and compact, I was wondering if the same can be done in a similar manner to FIELD.)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on @bill-karwin 's answer, this works perfectly:

    CREATE TABLE mytable (id int, language char(3), text text);  

INSERT INTO mytable VALUES 
  (0,'ENU','a'), 
  (0,'JPN','b'), 
  (0,'DAN','c'), 
  (1,'ENU','d'), 
  (1,'JPN','e'), 
  (1,'DAN','f'),
  (1,'RUS','g'),
  (1,'ESP','h');

SELECT * FROM mytable 
ORDER BY FIELD(Language, 'DAN', 'JPN', 'ENU') DESC, Language, ID;

    The difference is this sorts the unexpected values alphabetically after the custom sorted values.


  2. 0

    This is the one that puts unknown languages at the start:

    SELECT * FROM mytable 
ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID;

    Here are two solutions that put the unknown languages at the end:

    SELECT * FROM mytable 
ORDER BY FIELD(Language,'DAN','JPN','ENU') DESC, ID;

SELECT * FROM mytable 
ORDER BY 
  FIELD(Language,'ENU','JPN','DAN') = 0,
  FIELD(Language,'ENU','JPN','DAN'), ID;

    Dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search