Sort Days of the Week in SQL

Question

I have an Access database I am querying with SQL from VB .NET. The Table I'm querying has a column called "Day" which has the days stored as the text data type in Access. I need to select all the unique instances in the field so am currently using:

SELECT Day FROM Slot GROUP BY Day

But this returns the days in alphabetical order when I need them in chronological order. Now assuming that the only possible values it can take are "Monday", "Tuesday", "Wednesday" ,"Thursday" and "Friday", what would you recommend doing to sort them correctly?

Edit: The table is called Slot and has three columns; SlotNumber (AutoNumber), Day (Text), SlotTime (Text). An example might be:

SlotNumber: 6

Day: Monday

SlotTime: 2:40PM

could you show atleast some records that are stored in Day table? — John Woo
Are you sure you don't have an answer button at the bottom of this page? — yhw42
I do have an answer button, but I can't answer my own question within 8 hours. Not enough reputation. — Ben Elgar
I guess there's that. You should fix it (by editing the answer out of the question and adding it as an answer) when you get a chance. Glad you found something that worked for you! — yhw42

Steven Schroeder Steven Schroeder · Accepted Answer · 2012-02-11T03:58:58

If you are stuck with the data as is, I would recommend that you add an ORDER BY clause. Within the ORDER BY clause you will want to map each distinct value to a numeric value.

e.g., Using IIf

SELECT Slot.Day
FROM Slot
GROUP BY Slot.Day
ORDER BY IIf(Slot.Day = "Monday", 1,
         IIf(Slot.Day = "Tuesday", 2,
         IIf(Slot.Day = "Wednesday", 3,
         IIf(Slot.Day = "Thursday", 4,
         IIf(Slot.Day = "Friday", 5)))));

e.g., Using SWITCH

SELECT Slot.Day
FROM Slot
GROUP BY Slot.Day
ORDER BY SWITCH(Slot.Day = 'Monday', 1,
                Slot.Day = 'Tuesday', 2,
                Slot.Day = 'Wednesday', 3,
                Slot.Day = 'Thursday', 4,
                Slot.Day = 'Friday', 5);

Sort Days of the Week in SQL

2 Answers