Putting Specific MySQL Field Values First or Last in a Query Result

When querying records in MySQL, it is sometimes useful to force rows with certain field values to appear either at the beginning or at the end of the result set.

A common example is sorting Nobel Prize records for the year 1984 while treating the subject values 'Physics' and 'Chemistry' specially. This can be done with either an IN expression or a CASE expression in ORDER BY.

Move specified values to the end

Requirement: rows whose subject is 'Physics' or 'Chemistry' should be listed last.

One simple way is to sort by whether the column value is in a given list:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Physics','Chemistry'), subject, winner;

Another way is to use a CASE expression and assign a sorting weight to each group:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY
    (case subject
         when 'Chemistry' then 1
         when 'Physics'   then 1
         else 0
        end), subject, winner;

-- 等价于：

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY
    (case subject
         when 'Chemistry' then 0
         when 'Physics'   then 0
         else 1
        end) DESC, subject, winner;

Move specified values to the front

Putting specified values first is essentially the reverse of putting them last. You can invert the condition, or use the same expression and add DESC to reverse the order.

Requirement: rows whose subject is 'Physics' or 'Chemistry' should be listed first.

Using NOT IN:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject NOT IN ('Physics','Chemistry'), subject, winner;

Using CASE:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY
    (case subject
         when 'Chemistry' then 0
        when 'Physics'   then 0
        else 1
     end), subject, winner;

-- 等价于：

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY
    (case subject
         when 'Chemistry' then 1
        when 'Physics'   then 1
        else 0
     end) DESC, subject, winner;

How the two approaches differ

The first approach relies on the result of the IN expression. In MySQL, when the field value appears in the list, the expression evaluates to 1; when it does not, it evaluates to 0. Since sorting is ascending by default, 0 comes before 1. That is why rows outside the value list appear first, and rows inside the list are pushed to the end.

The same idea works in reverse with NOT IN, or by applying DESC to the expression.

One detail is worth noting: if the requirement only says that 'Physics' and 'Chemistry' should be first or last, the relative order between those two values is not controlled by the first sorting expression when they share the same sort value. Their order will then be determined by the later sort fields, such as subject and winner in these examples.

If the order between specific values must be controlled precisely, CASE is more flexible. It lets you assign different numeric weights to different values, so the sorting priority is explicit.

For example, if 'Physics' and 'Chemistry' both need to appear at the front, but 'Physics' must come before 'Chemistry', the assigned weights only need to satisfy this order:

Physics < Chemistry < everything else

That can be written as:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY
    (case subject
         when 'Chemistry' then 1
        when 'Physics'   then 0
        else 2
     end), subject, winner;