Custom Sorting MySQL Data

Sorting data in MySQL can be really easy if you're just trying to sort alphabetically or numerically. All you have to do is slap in the ORDER BY clause and list out the columns in order of importance. For example:

SELECT * FROM `animals` ORDER BY `color` DESC,`name`
name animal_type color
Clifford Dog light blue
Jingles Cat light blue
Lucy Cat light blue
Sebastian Hamster light blue
Cincy Tiger dark blue
Odysseus Hamster dark blue
Steve Bear dark blue
Vito Dog dark blue
Billy Bear blue
Rex Lion blue
Rover Dog blue
Simba Lion blue

But, what if you want to do something more complicated? Like sorting the animals by the lightness of their color? That's where ORDER BY FIELD can come in handy. You can use it to specify the exact order the values should be sorted by. Check this out:

SELECT * FROM `animals` ORDER BY FIELD(`color`,'light blue','blue','dark blue'),`name`
name animal_type color
Clifford Dog light blue
Jingles Cat light blue
Lucy Cat light blue
Sebastian Hamster light blue
Billy Bear blue
Rex Lion blue
Rover Dog blue
Simba Lion blue
Cincy Tiger dark blue
Odysseus Hamster dark blue
Steve Bear dark blue
Vito Dog dark blue

Still, that doesn't quite cover all the bases. What if you want to show all the light blue and blue animals first (sorted by name) followed by all the dark blue animals? You could do two separate queries and then UNION them together, but that creates it's own set of issues... Or you can try this cool trick involving CASE that I figured out the other day:

SELECT *,CASE WHEN `color` IN ('light blue','blue') THEN '1' WHEN `color` ='dark blue' THEN '2' END AS `customsort` FROM `animals` ORDER BY `customsort`,`name`
name animal_type color
Billy Bear blue
Clifford Dog light blue
Jingles Cat light blue
Lucy Cat light blue
Rex Lion blue
Rover Dog blue
Sebastian Hamster light blue
Simba Lion blue
Cincy Tiger dark blue
Odysseus Hamster dark blue
Steve Bear dark blue
Vito Dog dark blue

Using the CASE statement, you create a temporary column that you can then sort by. Of course, this example is really simple but you can get much more complex with the queries.


Comments

Loading…

This post was published on December 16th, 2009 by Robert James Reese in MySQL. Before using any of the code or other content in this post, you must read and agree to our terms of use.