Explicit Order for 'ORDER BY' Clause in MySQL

You can explicit specify the order with which the 'ORDER BY' statement sort the data in MySQL. Lets say you have a priority field that has the values "Low" "High" or "Medium" - this is what you should do to sort the data from the highest to the lowest priority.

SELECT * FROM <table> ORDER BY priority='High' DESC, priority='Medium' DESC, priority='Low" DESC

I recently had to use this in the second version of Nexty - there was a field called 'section'(enum type). The possible rows where 'Idea', 'Immediately', 'Someday/Maybe', 'Waiting' and 'Done'. I sort this using the following query...

SELECT id,name,description,url,project_id,type,sort_order, 
DATE_FORMAT(edited_on,'%d %b %h:%i %p') AS time 
FROM Task WHERE user_id='$_SESSION[user]
ORDER BY type='Immediately' DESC, type='Someday/Maybe' DESC, 
type='Waiting' DESC, type='Idea' DESC, type='Done' DESC, 
sort_order DESC, edited_on DESC
Subscribe to Feed