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.