ENUM and ORDER BY FIELD - customized sorting in mysql

ENUM and ORDER BY FIELD - customized sorting in mysql


How many times have you heard that and cringed? Didn't marketing people learn the alphabet when they were young?

Well, throw away those awful CASE WHEN... hacks and subquery tables. Make way for ENUM and ORDER BY FIELD!

The fastest way to deal with a customized sort is by using ENUM. ENUM will sort your column in the order you list the possible values. For example,-- Table structure for table `fieldSort` CREATE TABLE IF NOT EXISTS `fieldsort` ( `id` int(11) NOT NULL AUTO_INCREMENT, `column` enum('start','pending','finished') NOT NULL, PRIMARY KEY (`id`) ); -- Dumping data for table `fieldSort` INSERT INTO `fieldSort` (`id`, `column`) VALUES (1, 'pending'), (2, 'start'), (3, 'start'), (4, 'finished'), (5, 'pending'), (6, 'finished');


Now run:SELECT * FROM `fieldSort` ORDER BY `column`


Magic!

But, suppose you cannot use ENUM on this field for some reason, or you need to do further manipulations? ORDER BY FIELD to the rescue!

Here is that same table, without the ENUM:CREATE TABLE IF NOT EXISTS `fieldsort` ( `id` int(11) NOT NULL AUTO_INCREMENT, `column` varchar(25) NOT NULL, PRIMARY KEY (`id`) );


Now try:SELECT * FROM `fieldSort` ORDER BY FIELD (`column` , 'start', 'pending', 'finished');


Voila! as the Belgians say.

But what happens if you don't include all of the possible values in your list?

Add INSERT INTO `test`.`fieldSort` (`id` ,`column`) VALUES (NULL , 'approved'); and we'll see...

The values not in the list show first, in the order they were entered in the table, then the values you asked to order. You can see this more clearly by leaving out 'start' and 'approved' both:SELECT * FROM `fieldSort` ORDER BY FIELD (`column` , 'pending', 'finished')


So why use ORDER BY FIELD? While it's true that ENUM is faster, and you will need to have a good list of your values to make this really work out, I think you will find plenty of occasions where an ENUM was not used and you'll be happy to have this as a solid backup in your bag of tricks.

Hope that was helpful!