Ask the Expert

How do I put days of the week in order...Monday, Tuesday, Wednesday, etc?

How do I put days of the week in order...Monday, Tuesday, Wednesday, etc.?


    Requires Free Membership to View

If you want to order by the day of the week, then just order by column which contains the date. For testing purposes, I've set up a little test table which contains only one column (DAY). Below is a query which shows the contents of that column:

   SQL> select day,to_char(day,'DAY') from test;

   DAY       TO_CHAR(D
   --------- ---------
   01-MAY-01 TUESDAY
   02-MAY-01 WEDNESDAY
   03-MAY-01 THURSDAY
   04-MAY-01 FRIDAY
   05-MAY-01 SATURDAY
   06-MAY-01 SUNDAY
   07-MAY-01 MONDAY
   08-MAY-01 TUESDAY
   09-MAY-01 WEDNESDAY
   10-MAY-01 THURSDAY

Now, they were inserted into this table in sorted order. So let's see how to put them in *reverse* order!

   SQL> select day,to_char(day,'DAY') from test
     2  order by day desc;

   DAY       TO_CHAR(D
   --------- ---------
   10-MAY-01 THURSDAY
   09-MAY-01 WEDNESDAY
   08-MAY-01 TUESDAY
   07-MAY-01 MONDAY
   06-MAY-01 SUNDAY
   05-MAY-01 SATURDAY
   04-MAY-01 FRIDAY
   03-MAY-01 THURSDAY
   02-MAY-01 WEDNESDAY
   01-MAY-01 TUESDAY

Notice that the days of the week go into reverse order now! I've just used the ORDER BY clause on the column of DATE datatype.

If you want to group all of the "Tuesdays" together, then you'll have to order by the TO_CHAR column as can be seen in the following example:

   SQL> select day,to_char(day,'DAY') from test
     2  order by 2;

   DAY       TO_CHAR(D
   --------- ---------
   04-MAY-01 FRIDAY
   07-MAY-01 MONDAY
   05-MAY-01 SATURDAY
   06-MAY-01 SUNDAY
   03-MAY-01 THURSDAY
   10-MAY-01 THURSDAY
   01-MAY-01 TUESDAY
   08-MAY-01 TUESDAY
   02-MAY-01 WEDNESDAY
   09-MAY-01 WEDNESDAY

Notice that all of the Wednesday's are together, etc. But if you want this sorted further so that all of the Monday's fall before the Tuesday's, then you'll need one more trick. You'll have to use the TO_CHAR function again, but this time, get the day of the week as a number. For instance, see below:

   SQL> select day,to_char(day,'DAY'),to_char(day,'D') from test
     2  order by to_char(day,'D');

   DAY       TO_CHAR(D T
   --------- --------- -
   06-MAY-01 SUNDAY    1
   07-MAY-01 MONDAY    2
   01-MAY-01 TUESDAY   3
   08-MAY-01 TUESDAY   3
   02-MAY-01 WEDNESDAY 4
   09-MAY-01 WEDNESDAY 4
   03-MAY-01 THURSDAY  5
   10-MAY-01 THURSDAY  5
   04-MAY-01 FRIDAY    6
   05-MAY-01 SATURDAY  7

Notice now that all of the Tuesday's fall before the Wednesday's, etc.

I wasn't sure which way you wanted to sort your data so that is why I gave you three examples. Hopefully, one of these fit the bill!!

For More Information


This was first published in May 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: