Q

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.?


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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close