How to order by weekday (but not alphabetically!) ?

[timcclayton]

Hello all.
I am using a SELECT query to drag out records sorted by WEEKDAY. The trouble is, they sort themselves alphabetically (naturally) and this looks a bit stupid when Friday is the "first" day. Is there a way to sort by actual weekday order?

Code I am using is:

[code]"SELECT * FROM FLIGHTS ORDER BY 'intdom', 'day' ASC"[/code]

where "day" is the weekday field.

[Barand]

[code]"SELECT * FROM FLIGHTS ORDER BY intdom,
CASE
WHEN day='Sunday' THEN 0
WHEN day='Monday' THEN 1
WHEN day='Tuesday' THEN 2
WHEN day='Wednesday' THEN 3
WHEN day='Thursday' THEN 4
WHEN day='Friday' THEN 5
WHEN day='Saturday' THEN 6
END"[/code]

[effigy]

or [tt]date_format(day, '%w')[/tt]

[Barand]

I got the impression that 'day' column contained the day name and was not a DATE column

[effigy]

Good point. I'll go wake up... :)

[timcclayton]

Yes that's correct, the first example worked beautifully. Thanks very much Barand (and effigy too for trying to help) - much appreciated!