problem in Order By

[san1014]

Hi
I Have a table like this

code=oracle]

sql> select month from temp;
month
-----------
Apr
May
Jul
Jun
dec
Oct
Feb
Nov
Mar
Sep
Jan
Aug

Now i want the o/p like this
sql>select month from temp;

month
----------
Jan
Feb
Mar
Apr
may
Jun
Jul
Aug
Sep
Oct
Nov
Dec

[/code]


Is there any special function to get the months values in order?

[amitpatel66]

Try this:

SELECT col1,TO_CHAR(TO_DATE('01-'||col1||'-2007','DD-MON-YYYY'),'MM') col2 FROM
(SELECT 'OCT' AS col1 FROM dual
UNION
SELECT 'FEB' FROM dual
UNION
SELECT 'MAR' FROM dual
UNION
SELECT 'DEC' FROM dual
UNION
SELECT 'JUN' FROM dual
UNION
SELECT 'SEP' FROM dual
UNION
SELECT 'JUL' FROM dual
UNION
SELECT 'AUG' FROM dual
UNION
SELECT 'APR' FROM dual
UNION
SELECT 'JAN' FROM dual
UNION
SELECT 'NOV' FROM dual
UNION
SELECT 'MAY' FROM dual)
ORDER BY col2
/
 

[singhh04]

Just you need the following sql to excute; you will get the months as per your order :-

SELECT month FROM temp ORDER BY to_date(month,'MON');

[Dharmaraju]

Just you need the following sql to excute; you will get the months as per your order :-

SELECT month FROM temp ORDER BY to_date(month,'MON');

select * from (select to_char(hire_date,mm)aa ,to_char(hire_date,month), aa1 from employees ) a order by aa