MySQL – Custom ORDER BY field
Posted on 05 March 2010 by Jason Grimme
Today at work I had to pull a large set of data out of a database. The data needed to be displayed in a particular order by multiple fields/columns.
Normally this is as simple as using ORDER BY column_name ASC, etc.
However I needed to sort by ticket status IDs which were not in numerical order. I needed 10 to be first, followed by 2, and then 6.
Rather than having PHP do the sorting, I found a MySQL function that allows you to sort by a custom order. The function is named FIELD(). The first parameter is the field name, and all subsequent parameters are the order you want to sort by. You may attach ASC or DESC as you would usually to reverse.
Example:
1 2 3 4 5 6 7 | SELECT * FROM tickets ORDER BY FIELD(tickets.status_id, 10, 2, 6), tickets.date_created ASC |
You do not have to only use numbers, you can use strings as well.
1 2 3 4 5 6 | SELECT * FROM tickets ORDER BY FIELD(tickets.day, 'Tues', 'Sat', 'Mon', 'Fri') DESC |
Tags | MySQL