Home » SQL & PL/SQL » SQL & PL/SQL » group the data on month and sort it on date  () 1 Vote
group the data on month and sort it on date [message #385850] Wed, 11 February 2009 07:28 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
hi all,

i am grouping some data on month using to_char function .
i.e to_char(Date,'MON-YY') but it sort the data on the charachter set

i want to sort the resulting data by month and year .

Dec-2008
Jan-2009
Feb-2009
so on

is there any way to do it?

thanks
Re: group the data on month and sort it on date [message #385851 is a reply to message #385850] Wed, 11 February 2009 07:44 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
order by {date_column}


QED.
Re: group the data on month and sort it on date [message #385852 is a reply to message #385851] Wed, 11 February 2009 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Wed, 11 February 2009 13:44
order by {date_column}


QED.


Surely that'd give:
ORA-00979: not a GROUP BY expression

you can always to_date your to_char'ed date.
Re: group the data on month and sort it on date [message #385853 is a reply to message #385852] Wed, 11 February 2009 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
OP says they are "grouping" data, but I don't see any code so I am not going to assume they are using a GROUP BY statement. Lack of specifics in a question can get you answers such as mine.
Re: group the data on month and sort it on date [message #385856 is a reply to message #385850] Wed, 11 February 2009 08:10 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
You could group by trunc(date_column, 'MM'), then just order by that expression.

e.g
select count(*), to_char(trunc(date_column, 'MM'), 'MON-YY') 
from my_table 
group by trunc(date_column, 'MM') 
order by trunc(date_column, 'MM');
Re: group the data on month and sort it on date [message #385937 is a reply to message #385856] Wed, 11 February 2009 23:33 Go to previous message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Sorry i guess my question was not so clear thats why you people are having differnnt interpretations.

Anyway thanks for the help. this one working fine...

select count(*), to_char(trunc(date_column, 'MM'), 'MON-YY') 
from my_table 
group by trunc(date_column, 'MM') 
order by trunc(date_column, 'MM');


i have found another way.

select count(*), to_char(date_column, 'MON-YY') 
from my_table 
group by to_char(date_column, 'MON-YY'),to_char(date_column, 'YYYYMM') 
order by to_char(date_column, 'YYYYMM') ;

[Updated on: Wed, 11 February 2009 23:33]

Report message to a moderator

Previous Topic: date substraction
Next Topic: doubt with case
Goto Forum:
  


Current Time: Sun Dec 04 14:31:38 CST 2016

Total time taken to generate the page: 0.09599 seconds