Is it possible to write custom ORDER BY statement in MySQL
I mean, I have database like this,
id time day 1 1:55 Mon 2 2:00 Sun 3 1:00 Mon 4 3:00 Tue 5 2:30 Sun I can write order statement like this,
SELECT * FROM mytable ORDER BY day but is it possible to write query to get Sun as first and Tue for second and Mon for third
This is wrong but like this query:
SELECT * FROM mytable ORDER BY Sun, Tue, Mon Its possible
SELECT * FROM mytable ORDER BY FIELD(day, Sun, Tue, Mon) Assuming that day is a VARCHAR you can write a lengthy CASE WHEN statement:
SELECT * FROM <table> ORDER BY CASE day WHEN 'Sun' THEN 1 WHEN 'Mon' THEN 2 WHEN 'Tue' THEN 3 WHEN 'Wed' THEN 4 WHEN 'Thu' THEN 5 WHEN 'Fri' THEN 6 WHEN 'Sat' THEN 7 ELSE 0 END The comment from arilia is the best way for mysql. For more portable sql try
order by day = 'sat', day = 'Fri' And carry on for the rest of the days. That should work for all databases
Try this Check
SELECT * FROM table ORDER BY FIELD(day, Sun, Tue, Mon,Tue,Wed,Thu,Fri,Sat) You can use Custom order query of Mysql like below:
SELECT * FROM mytable ORDER BY FIELD(day, 'Sun', 'Tue', 'Mon') For more information you can see the documentation :http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_field
