William Duffy

Glasgow Based C# ASP.NET Web Developer

Order by day column using SQL

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.


Tagged as , , , + Categorized as SQL Server

2 Comments

  1. Yo Duffy!

    DRY it up! Extract the Case to a scalar valuer function and order on that!

    CREATE FUNCTION Convert_DayNameToDayNumber
    (
    @DayOfTheWeek nvarchar(10)
    )
    RETURNS int
    AS
    BEGIN
    RETURN CASE @DayOfTheWeek
    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
    END
    GO

    ——————————

    SELECT
    *
    FROM
    classes
    ORDER BY
    Convert_DayNameToDayNumber(Day) ASC

  2. Awesome thing William. Dave, that takes it to the next level. But then, you should fire the person that wrote a system that saved the day of the week as a string!

Leave a Reply