Bringing specific results to the front using one MySQL query.

Question

I'm setting up my geolocation feature which lists out all the countries in order of name. However, most websites have it set up where the popular countries (such as United States, United Kingdom, Canada, Australia) are at the front of the list (are shown first rather than in their assigned places). Is it possible to achieve this using the same MySQL query that I am already running to select them?

SELECT * FROM `global_flags` ORDER BY `country`

Is there a better alternative somewhere? Perhaps something that can reorganize the results after the database has already been queried? I don't want to exclude those results and then manually type them at the beginning because that would waste more space in the file.

Answer 1

Use a CASE expression in your ORDER BY clause, and assign specific values to the countries you want to appear first.

e.g.

SELECT * FROM global_flags
  ORDER BY (CASE WHEN country="United States" THEN 0 WHEN country="United Kingdom" THEN 1 ELSE 2 END), country;

Answer 2

If the order can't be surmised from the column itself (and in your case I don't think you can), you'll need to add another column to your table (or add it in via a view) to supply the sort order.

For instance, you could have a priority column of type int. For most rows it's empty, and for those you want up front it could be 1. Say you want USA and UK as your first rows, and alphabetical for the rest:

SELECT * FROM global_flags ORDER BY priority DESC, country

will push the higher-priority items to the top of your result set. Items with the same priority class will be alphabetically sorted.

EDIT: My approach has the con that you're adding UI stuff to the data. You could always separate the priority - a UI thing - into its own table and either INNER JOIN it in or create a view. But perhaps VeeArr's answer is the better one: it doesn't pollute your data with UI concerns.