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