Sorting Results Arbitrarily in MySQL
Dec 3, 2013 · 1 minute readCategory: mysql
When you have a result set in MySQL that you need to order in a specific way you might think that it is not possible and instead you are going to have to read all of your results into your application domain (eg PHP) and manage the sorting from there. On a large results set that could be seriously inefficient.
The truth is that you don’t have to do that. You can specify any kind of sort order including sorting by string values as you see fit.
The way to do this is to use the magic of FIND_IN_SET combined with ORDER BY.
For a full example see this query that is pulling out product images from a data repository in descending image size as described in one of the columns as a string.
SELECT
dcl.prodID,
dcl.contentGuid,
dc.url,
dcmv.metaValueName AS resolution
FROM cds_DigContent dc
JOIN cds_DigContent_Links dcl
ON (dc.contentGuid = dcl.contentGuid)
JOIN cds_DigContent_Meta dcm
ON (dcm.contentGuid = dc.contentGuid
AND dcm.metaAtrId = 6)
JOIN cds_DigContent_Meta_ValVoc dcmv
ON (dcmv.metaValueId = dcm.metaValueId)
WHERE dc.mediaTypeId = 15
AND dcl.prodId = '{$this->getCnetProdID()}'
ORDER BY
FIND_IN_SET(dcmv.metaValueName, '2400 x 1800,1200 x 900,1024 x 768,800 x 600,640 x 480,400 x 300,200 x 150,75 x 75')
LIMIT 0, 1