SQL Ordering: Ordering by a specific field, while moving one or more items to the beginning or end of the results

sql_ordering.sql

/*
  In the database, there is a table called `countries` with a `name` field.
  The database contains the following entries for countries:
  Canada, Mexico, Argentina, Brazil, United States, Ireland, North Korea, Other
*/


/* 
  Order by a specific field, while moving one or more items to the beginning
*/

/* Single Item ('United States' at the beginning of results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name`='United States' then -1 ELSE 0 END, name

/* Multiple Items ('Canada' and 'United States' at the beginning of the results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name` IN ('Canada', 'United States') then -1 ELSE 0 END, name


/* 
  Order by a specific field, while moving one or more items to the end
*/

/* Single Item  ('Other' at the end of results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name`='Other' then 1 ELSE 0 END, name

/* Multiple Items ('North Korea' and 'Other' at the end of the results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name` IN ('North Korea', 'Other') then 1 ELSE 0 END, name


/* 
  Order by a specific field, while moving one or more items to the beginning and one or more items to the end
*/

/* Single Item ('United States' at the beginning of results and 'Other' at the end of the results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name`='United States' then -1 ELSE 0 END, CASE WHEN `name`='Other' then 1 ELSE 0 END, name

/* Multiple Items ('Canada' and 'United States' at the beginning of the results and 'North Korea' and 'Other' at the end of the results) */
SELECT *
FROM `countries`
ORDER BY CASE WHEN `name` IN ('Canada', 'United States') then -1 ELSE 0 END, CASE WHEN `name` IN ('North Korea', 'Other') then 1 ELSE 0 END, name