MySQL – Returning Rows in a Specific Order

MySQL – Returning Rows in a Specific Order

The MySQL ORDER BY clause let’s you easily order in fields in ascending or descending order however occasionally you’ll want to have a specific order

Take for instance if you have a list of products on a sales site ordered by name. Your boss tells you he wants to show one (or several) products before all others on the page. At this point you have 2 options – add a ‘special’ column in the table and do an ORDER BY special, name – which would only work if he wanted these special products in alphabetical or reverse alphabetical order – or use a UNION with multiple SELECT queries. Neither are great options. This is where the FIELD() function comes in.

The rows ordered by FIELD() will appear at the end of your table, so to get them appearing at the top just add DESC and put enter your FIELD() arguments in reverse order. All rows not specifically ordered with FIELD() will remain unordered unless specified otherwise. Here’s an example.

If the ID of the products your boss wants to appear first are 36, 40, 12 respectively, we can order like so:

SELECT * FROM products ORDER BY FIELD(id, 12, 40, 36) DESC, name

There is no limit to the number of arguments you can add and they must be the same data type as your field.