MySQL fields are made to order alphabetically or numerically on fields, but what happens if you have a custom sort order?

You might ask, but when would you have a custom sort order?

The two situations that come to mind are when a field contains data NOT in the expected format. Imagine a field with days of the week entered alphabetically! Or numbers entered numerically? Such that you have a table:

day qty name score
Tuesday five Amber 500
Monday three Bob 450
Friday four Charlie 350
Sunday six Dave 550
Thursday one Erin 200
Wednesday two Erin 200
Thursday nine Fred 250

As you can see it’s already sorted by Name, but if you wanted to sort it by the ‘day’ or ‘qty’ fields you should expect that it won’t be sorted by anything functional.

ie: if you sorted the table by day, alphabetically ascending you would get:

day qty name score
Friday four Charlie 350
Monday three Bob 450
Sunday six Dave 550
Thursday one Erin 200
Thursday nine Fred 250
Tuesday five Amber 500
Wednesday two Erin 200

likewise, ‘qty’ is also going to give you undesired output:

day qty name score
Tuesday five Amber 500
Friday four Charlie 350
Thursday nine Fred 250
Thursday one Erin 200
Sunday six Dave 550
Monday three Bob 450
Wednesday two Erin 200

What we need is a way to resolve the sort order to be: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and then Saturday; or One, Two, Three, Four, Five, etc…

How do we resolve this?

Using the MySQL Field() function it is possible to define the order which will return the index of the field value with the desired sort string. Using the days of the week as an example:

SELECT * FROM table_name
ORDER BY FIELD(day,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') ASC

will return:

day qty name score
Sunday six Dave 550
Monday three Bob 450
Tuesday five Amber 500
Wednesday two Erin 200
Thursday one Erin 200
Thursday nine Fred 250
Friday four Charlie 350

Similarly:

SELECT * FROM table_name
ORDER BY FIELD(qty,'one','two','three','four','five','six','seven','eight','nine','ten') DESC

will return:

day qty name score
Thursday nine Fred 250
Sunday six Dave 550
Tuesday five Amber 500
Friday four Charlie 350
Monday three Bob 450
Wednesday two Erin 200
Thursday one Erin 200

Enjoy!

I originally found this as a result of needing to sort a database table based on days of the week. I found my solution and adapted this page as a result of a post by Joe Sexton which can be found here: Thanks Joe.

Leave a comment

Your email address will not be published. Required fields are marked *