Home » SQL & PL/SQL » SQL & PL/SQL » Order by in Date Column (Oracle 116)
Order by in Date Column [message #584956] Mon, 20 May 2013 14:22 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,

I am trying group by count on Monthly basis,
entered_timestamp Column is of DATE datatype.
select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments where type = 'BOOK'  group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC

Above SQL doesn't gives the output in proper order ,
can someone suggest a workaround for this.

Thanks
Re: Order by in Date Column [message #584957 is a reply to message #584956] Mon, 20 May 2013 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ to_char(entered_timestamp,'YYYY-MM')

2/ Use a column trunc(entered_timestamp,'MONTH') that you will not display:
SQL> col mnth noprint
SQL> select trunc(sysdate,'MONTH') mnth, to_char(sysdate,'MM-YYYY') from dual;
TO_CHAR
-------
05-2013

Regards
Michel
icon14.gif  Re: Order by in Date Column [message #584959 is a reply to message #584957] Mon, 20 May 2013 14:39 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Thanks a Lot, I used the First one.
Re: Order by in Date Column [message #585075 is a reply to message #584959] Tue, 21 May 2013 14:55 Go to previous message
Bill B
Messages: 1086
Registered: December 2004
Senior Member
You could have also done the following. As long as you use a column from the select you can do anything to it.

select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY 
from payments 
where type = 'BOOK'  
group by to_char(entered_timestamp,'MM-YYYY') 
order by to_date(to_char(entered_timestamp,'MM-YYYY'),'MM-YYYY');
Previous Topic: Cursor and variable issue.
Next Topic: lock table (2 Merged)
Goto Forum:
  


Current Time: Fri Aug 29 21:48:15 CDT 2014

Total time taken to generate the page: 0.09902 seconds