MySQL: Sort By Column Values

Tuesday, December 13, 2011

In MySQL, sorting the data using columns can easily be done by using the ORDER BY clause. Recently, I came across a requirement where I had to sort the data according to the specific values of a column. For example, if a column has the numbers written as words (one, two, three etc.), the values need to be sorted according to their numerical equivalent rather than the default alphabetical sorting.

By default, the ORDER BY clause will sort data using alphanumeric sorting. But there is a trick by using the MySQL field(str,str1,str2,str3...) function to give a different order to the values in the columns. The function finds the position of str in the list of strings str1, str2, str 3...

Here is an example on how you can combine the field function with the ORDER BY clause:

ORDER BY field(`column_name`, 'One', 'Two', 'Three', 'Four', 'Five')

The above code will sort the data according to the column values One, Two, Three, Four, Five.