Is it possible to select a specific ORDER BY in SQL Server 2008?

Question

I have a table that holds days and times, the day column, can have any of the seven days entered into it, and they are set to data type varchar. As this table holds booking times for a client, I want to select all days from the table where the id matches, and I want to sort by day Monday-Sunday. I was hoping that I could add something to this query to manually select the order the results come back like so:

select * 
from requirements 
where Family_ID = 1 
ORDER BY Day, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

This of course doesn't work but I just wanted to show what I am trying to achieve. The client doesn't necessarily require help every day, I just want to show the days they are booked in.

Sorting by DESC and ASC doesn't help with days of the week, I would appreciate any tips on how to achieve this.

Thanks.

Would using the DATEPART(weekday,@dtDate) function help? It would give you the days of the week as numbers — Chetter Hummin, Apr 18 '12 at 11:11

score 40 · Accepted Answer · answered Apr 18 '12 at 11:12

40

Hmm.. that's nasty, the days are stored as verbatim 'Monday', 'Tuesday', etc?

Anyway, just do this:

SELECT * 
FROM Requirements
ORDER BY 
     CASE Day 
     WHEN 'Monday' THEN 1
     WHEN 'Tuesday' THEN 2
     WHEN 'Wednesday' THEN 3
     WHEN 'Thursday' THEN 4
     WHEN 'Friday' THEN 5
     WHEN 'Saturday' THEN 6
     WHEN 'Sunday' THEN 7
     END

answered Apr 18 '12 at 11:12

Michael Buen

38,643
9
94
118

Hi there your solution worked, thank you so much! That is another thing learned! I have no votes left today, but when they are refreshed I will vote up your answer. Can I just ask what you mean about my way of doing things being nasty, I felt it wan't the best way to do this. The joys of being a beginner! – deucalion0 Apr 18 '12 at 11:15
1

Either use DATETIME, or use an integer for week days, e.g. 0 = Saturday, 1 = Monday, 2 = Tuesday, etc. That way, you can naturally sort the field, either by: `ORDER BY DATEPART(WEEKDAY, DateTimeFieldHere)`, or this: `ORDER BY DateTimeFieldHere` – Michael Buen Apr 18 '12 at 11:34
1

If re-designing the table is not an option for you at this point in time, just add a computed column for those `CASE WHENs`. That way, you can sort on indexable field. Computed column can speed up sorting or filtering. e.g. http://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/ http://blog.sqlauthority.com/2010/08/13/sql-server-computed-column-and-performance-part-3/ – Michael Buen Apr 18 '12 at 11:49
It is not a problem to re design the table, I intend on doing this as correctly as possible, that is why all your advice is important. It will all come to use that is for sure! Thank you! :) – deucalion0 Apr 18 '12 at 11:53
This is an excellent solution! For me -at least- who need to order things by putting tuples that have a specific value first in the enumeration each time. For instance, and borrowing the example above, I need to order tuples in the order 'Friday', 'Thursday', 'Wednesday',... etc. at one point and 'Sunday', 'Monday', 'Tuesday', at another. I even have to deal with complex orders like: 'Thursday', 'Monday', 'Sunday', 'Wednesday',... etc. So, thanks again for this wonderful solution! – katamayros Dec 15 '15 at 17:02

score 1 · Answer 2 · answered Apr 18 '12 at 12:06

IMHO you don't have any reason to store Monday, Tuesday, etc ... If you store the date or datetime value, you can always extract the weekday name from that data at query time when you need it - either using DATENAME in your query or using the functionality in your presentation tier. The performance cost of doing so doesn't justify storing it separately IMHO. And you should still be able to sort correctly using the native date/datetime data.

Is it possible to select a specific ORDER BY in SQL Server 2008?

2 Answers2

Linked