Advanced mySQL queries


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

Article Highlights

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')

Support website running for FREE, thanks!

If you find this post helpful and if you are thinking of buying from Amazon, please support the running cost of this website at no extra cost to you by searching and buying through the search box below. Thank you very much for your help!

Edward Chung

Edward Chung aspires to become a full-stack web developer and project manager. In the quest to become a more competent professional, Edward studied for and passed the PMP Certification, ITIL v3 Foundation Certification, PMI-ACP Certification and Zend PHP Certification. Edward shares his certification experience and resources here in the hope of helping others who are pursuing these certification exams to achieve exam success.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *