Home > Web > PHP >

MySQL Arbitrary Ordering

Posted Jul 1, 2004
Last Updated Jan 22, 2008
Arbitrary Ordering in MySQL

Note: The examples in this article use the world database which you can download from http://www.mysql.com

One frequent problem encountered in MySQL programming is how to get your data in the order that you want. Many times the simple ORDER BY clause is insufficient.

For example, let’s say you know that you are interested in countries where the following languages are spoken: Urdu, Zulu, and Wolof. To find those countries you would be simple:

SELECT * FROM countrylanguage WHERE Language IN (‘urdu’, ‘zulu’, ‘wolof’);

This returns a table of the countries in no particular order.

What if you are most interested in Wolof? You would like country where Wolof is spoken to be returned first.

SELECT * FROM countrylanguage WHERE Language IN (‘urdu’, ‘zulu’, ‘wolof’) ORDER BY Language

This will return Urdu countries first, where ORDER BY Language DESC will return Zulu first.

How can this be done? Some suggest using a temporary table and a join, but I prefer a more elegant (and easier to program) solution. Drum roll please… The CASE statement.

SELECT *,
CASE Language
WHEN 'Wolof' THEN 1
WHEN 'Zulu' THEN 2
WHEN 'Urdu' THEN 3
ELSE NULL
END AS orderMe
FROM countrylanguage
WHERE Language IN ('Urdu', 'Zulu', 'Wolof')
ORDER BY orderMe;

Beautiful, ain’t she? What is going on here?
First, SELECT * will select all columns.
Second, the CASE statement tests the “Language” column. Skip down to END, this is where the structure of the CASE statement ends. After that, AS sets a column name of “orderMe” for the output of the case statement. Back up to the WHERE clauses. In plain English, what happens is if the Language column has the value of ‘Wolof’ then set orderMe to be 1. If it’s ‘Zulu’ orderMe will be 2, etc. If is none of these, make orderMe NULL.
It then limits the list to the three languages, and most importantly orders the list by the values of orderMe.

This statement first returns the countries that speak Wolof, then Zulu, the Urdu.

Great, you say, how about some practical applications? All right.
E-Commerce: feature certain products at the top of the page by ordering based on an arbitrary list of product IDs.
Document Management: Present the user with a document that matches the language preferences of the user’s browser.
Non-supported languages: Sort using sort orders for language not natively supported by MySQL such as Klingon or that language you and your sister made up. (There are better ways to do this is you have control over your server.)

Knowledge is power. Power corrupts. You are now more corrupted.

Comment

No HTML Tags are permitted.

Thomas

Oct 11, 2011

Very useful bit of SQL, thank you!

I'd like to note that instead of using NULL for the ELSE statement it is better to assign a higher number (999 for example) than the other CASE options.

Let's say you are selecting an unknown number of items from a table. If the results include Zack and/or Zulie you want them to always appear at the top of the results with the rest of the results sorted alphabetically below them. It would work like this:

SELECT `first_name`,
CASE `first_name` WHEN 'Zack' THEN 1 WHEN 'Zulie' THEN 2 ELSE 999 END AS `order_me`
FROM `name_list`
ORDER BY `order_me`, `first_name`;

This will force Zack and Zulie to appear at the top of the list and all other names will be alphabetized below them. If NULL is used instead of 999 then Zack and Zulie will actually be forced to the *bottom* of the list, the opposite of the intended result.

m

Aug 13, 2010

This is dumb. MySQL already has a tool for this purpose:



SELECT *,

FROM countrylanguage

WHERE Language IN ('Urdu', 'Zulu', 'Wolof')

ORDER BY FIELD(Language, "Wolof", "Zulu", "Urdu");

Andrew Penry

Aug 13, 2010

Sure enough, you are correct. ORDER BY FIELD is another great way of doing this. You can also use  ORDER BY  FIND_IN_SET(Language,"Wolof,Zulu,Urdu") which takes a comma separated list in one string.

Maxime

Jul 29, 2014

I disagree. I've tested ORDER BY FIELD and it's very unstable (it basically never does what I want on my end).

The solution presented on this post has arrived after a few hours of research and (failed) attempts, and provided me with a syntax I had never heard about.

Thanks Andrew!

Max
Wall Worm plugins and scripts for 3ds Max