Advanced mySQL queries

Summary: This post assemblies a number of useful but not-commonly-used language constructs of mySQL.

Ordering of Results

---

Follow an Order Specified by a List

ORDER BY FIELD (column, 'sample A','sample B','sample C','sample D')

The result will be ordered by 1 – sample A, 2 – sample B, 3 – sample C, 4 – sample D

---

Ignore Some Characters when Ordering

ORDER BY REPLACE (column, '&', '') ASC

The REPLACE function in the ORDER BY portion of the query will allow the character specified to be replaced by another character when ordering the results. This is very useful as mySQL interpret the order of some characters, e.g. the space, &, (, differently from normal convention. Also, the REPLACE function can be nested like:

REPLACE( REPLACE( REPLACE(a.en, ')', ' '),'ish (','ish '),'DanceSport (Latin','DanceSport (Standard')

---