Tech Erudition

Encountered a "problem of sorting" today when attempting to display list of record
based on status values.

status value can be one of these (New, In Progress, Deferred, Closed)

ORDER BY status would result in (Closed, Deferred, In Progress, New)

It follows the alphabetical order. However, for our list New and In Progress carried more weight than Deferred and Closed.

MySQL provides CASE operator which can be handy in solving it.

ORDER BY CASE status WHEN 'New' THEN 1 WHEN 'In Progress' THEN 2 WHEN 'Deferred' THEN 3 ELSE 4 END;

MySQL also provides FIELD function that can be used to achieve the same:

ORDER BY FIELD (status, 'Closed', 'Deferred', 'In Progress', 'New') DESC;

CASE vs FIELD

* FIELD needed list of all values and DESC order in this case.
* CASE needed specific weight for each value and provided default handling (ELSE).
* CASE provides usage of LIKE and other operators on the target field.
  http://stackoverflow.com/questions/3579760/mysql-case-in-select-statement-with-like-operator

NOTE: 
Be sure to index the columns on which CASE / FIELD operates on to avoid performance penalty.
Avoiding LIKE comparison is also advised to take advantage of the indexing.