Thread: Order By Day Of week

I am having the select statement which is returning me the Day of the Week,ie. Mon, Tue, Wed etc.
I need to order these days starting from Sunday and ending on Saturday.
Kindly let me know how to write a SQL script for this.

To get the desired result in your question use :
select * from table order by to_char(date_column,'D') ;

In the employees table we have hire_date column which is of datatype DATE.
select hire_date from employees order by to_char(hire_date,'D');

To check this thoroughly try the below query:
select hire_date, to_char(hire_date,'Dy'), to_char(hire_date,'D') dt from employees order by dt;

Originally Posted by tonyrobert

to get the desired result in your question use : select * from table order by to_char(date_column,'d') ; in the employees table we have hire_date column which is of datatype date. select hire_date from employees order by to_char(hire_date,'d'); to check this thoroughly try the below query: select hire_date, to_char(hire_date,'dy'), to_char(hire_date,'d') dt from employees order by dt;

appreciate your quick reply. The above solution works if i have a date column but not the day of week string ( mon, tue etc) i have a sub query. My requirement is get the data for all the weekdays of a month along with the sum of everything and then order by day of week. So my query looks like this (get the rows from ((get the data for day of week-7 rows - from (get the data for each day-30 rows)) union (get the sum of 30 days)) order by day of week eg: innermost subquery ( sub 1) - (date, day of week, value)( returned column) next level subquery ( sub 2) - (day of week, value) total valued query ( total 1) - (null, value) outer most query - day of week, value - order by day of week outer query from ( sub2 from ( sub 1)) union total 1) order by day of week.

Dear Vasanth ,

Post your table sructure and sample data to get quick reply from members.

Regards
Krishna

SELECT *
FROM
ORDER BY TO_CHAR(datecolumn,'D');

SELECT last_name, hire_date, to_char(hire_date,'DAY') DAY from employees ORDER BY TO_CHAR(hire_date-1,'D');

OR

SELECT last_name, hire_date, "DAY" from
(select case when TO_CHAR(hire_date,'D') = 2 then 1
when TO_CHAR(hire_date,'D') = 3 then 2
when TO_CHAR(hire_date,'D') = 4 then 3
when TO_CHAR(hire_date,'D') = 5 then 4
when TO_CHAR(hire_date,'D') = 6 then 5
when TO_CHAR(hire_date,'D') = 7 then 6
when TO_CHAR(hire_date,'D') = 1 then 7 else 0 end "oDay",TO_CHAR(hire_date,'Day') "DAY", hire_date, last_name from employees order by "oDay")