If you’ve ever had to sort a SQL query’s resultset using an “order by” clause on a “day of the week” column then you will know it can be very easy, or very tricky, depending on the format that the day is stored in the database.
If you stored the day of the week in numerical format (1 = Sunday, 7 = Saturday) then sorting can be accomplished via a simple order by clause.
1 2 3 4 5 6
SELECT * FROM classes ORDER BY day ASC
However, if you have saved the day of the week in textual format then things become a little trickier.
The problem is “order by” on a textual value sorts alphanumerically from a-z and has no comprehension about the order of weekdays. In this situation Thursday comes before Wednesday, Saturday before Tuesday, etc. To get around this we need to let the SQL query know what order to handle the weekdays in.
Transact-SQL’s CASE expression is perfect for this task. All we have to do is assess the day string in the “order by” clause and return a numerical value depending on the result of the expression.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT * FROM classes ORDER BY CASE WHEN Day = 'Sunday' THEN 1 WHEN Day = 'Monday' THEN 2 WHEN Day = 'Tuesday' THEN 3 WHEN Day = 'Wednesday' THEN 4 WHEN Day = 'Thursday' THEN 5 WHEN Day = 'Friday' THEN 6 WHEN Day = 'Saturday' THEN 7 END ASC
The resultset will now be sorted based on the textual “day of the week” field, exactly as it would be if the field held a numeric value.
For more details on Transact-SQL’s CASE expression see http://msdn.microsoft.com/en-us/library/ms181765.aspx.