how to sort days in oracle

[rahulae]

help me with this, i need to sort the order to be like sun,mon,tue,wed ,thu,fri,sat

[r035198x]

You need to give more details than that to get help.

[rahulae]

i have to write a query in such a way i have to get no of employees joined on a particular day.

i have written the following query

select count(*)"no of emp joined",to_char (hiredate,'day' ) "day" from emp group by to_char(hiredat e,'day');

output:
no of emp joined day
2 friday
1 monday
2 saturday
2 sunday
3 thursday
3 tuesday
1 wednesday

but i want the days to be sorted in order starting from sun,mon,tue,wed ,thu,fri,sat

[r035198x]

For any arbitrary order sorting, check out amit's post in this thread.

[N002213F]

the hard and long way;

[CODE=oracle]
select
count(*) as NO_EMP_JOINED,
to_char(hiredat e,'day') AS DAY_OF_WEEK,
(case when DAY_OF_WEEK = 'Sunday' then 1
when DAY_OF_WEEK = 'Monday' then 2
when DAY_OF_WEEK = 'Tuesday' then 3
when DAY_OF_WEEK = 'Wednesday' then 4
when DAY_OF_WEEK = 'Thursday' then 5
when DAY_OF_WEEK = 'Friday' then 6
when DAY_OF_WEEK = 'Saturday' then 7
else 99
end) AS ORDERCOLUMN
from emp
group by to_char(hiredat e,'day')
order by ORDERCOLUMN
[/CODE]

and the easy way, Oracle saw you coming;

http://www.techonthenet.com/oracle/functions/to_char.php

check right at the bottom of the page and the question.

[N002213F]

select
    count(*) as NO_EMP_JOINED,
    to_char(hiredate,'day') AS DAY_OF_WEEK
from emp 
group by to_char(hiredate,'day')
order by decode(upper(substr(DAY_OF_WEEK, 1,2)), 'SU', 0, 'MO', 1, 'TU', 2,'WE', 3,'TH', 4,'FR', 5,'SA', 6, 99)

based on http://bytes.com/forum/thread823124.html

[rahulae]

Thanx for ur help it works