3

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
Gayan
  • 2,845
  • 7
  • 33
  • 60
  • 2
    did you see this answer? http://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql – arilia Dec 30 '13 at 11:59

5 Answers5

2

Its possible

SELECT * FROM mytable ORDER BY FIELD(day, Sun, Tue, Mon)
woopata
  • 875
  • 5
  • 17
  • 29
1

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
0

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

exussum
  • 18,275
  • 8
  • 32
  • 65
0

Try this Check

SELECT * FROM table ORDER BY FIELD(day, Sun, Tue, Mon,Tue,Wed,Thu,Fri,Sat)
Muhammad Rashid
  • 563
  • 1
  • 6
  • 25
0

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

Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90