Ordering by a Specific Field Value in MySQL

If you need to order results from a MySQL query by a specific field value first then there’s a couple of approaches you can use. Let’s use as an example a table of countries where we want to order alphabetically but put the United Kingdom first. The first method uses a CASE statement:-

SELECT * FROM countries
ORDER BY CASE WHEN name = 'United Kingdom' THEN 1 ELSE 2 END, name

A simpler alternative is to sort by a comparison (name <> 'United Kingdom'):-

SELECT * FROM countries
ORDER BY name <> 'United Kingdom', name

In both examples the value resulting from either the CASE statement or the comparison determines the order. So we could easily change this so that the ‘United Kingdom’ comes last by using DESC:-

SELECT * FROM countries
ORDER BY name <> 'United Kingdom' DESC, name