Oracle date / order by question
11,356
Solution 1
Try this
select monthyear,yr,month,count(req_id)
from
(
SELECT (to_char(req_date,'MM/YYYY')) as monthYear, to_char(req_date,'YYYY') yr, to_char(req_date,'mm') month, req_id
FROM REQUISITION_CURRENT t
) x
GROUP BY monthyear,yr,month
order by yr, month
Solution 2
Try this. It works and it's efficient, but looks a little messy.
select to_char(trunc(req_date, 'MM'),'MM/YYYY') as monthYear
,count(req_id) as count
from requisition_current
group
by trunc(req_date, 'MM')
order
by trunc(req_date, 'MM');
Solution 3
Please try
Select req_date, (to_char(req_date,'MM/YYYY')) from MY_TABLE order by req_date
You are free to add additional sort fields, even if they are the same field.
Author by
BourneAgain
Updated on October 11, 2022Comments
-
BourneAgain over 1 year
I want to select a date from oracle table formatted like select
(to_char(req_date,'MM/YYYY'))
but I also want to order the result set on this date format.I want them to be ordered like dates not strings.
Like this
09/2009 10/2009 11/2009 12/2009 01/2010 02/2010 03/2010 04/2010 05/2010 06/2010 07/2010 08/2010 09/2010 10/2010 11/2010 12/2010
Not like
01/2010 02/2010 03/2010 04/2010 05/2010 06/2010 07/2010 08/2010 09/2009 09/2010 10/2009 10/2010 11/2009 11/2010 12/2009 12/2010
Any way to do this in sql?
Full SQL is:
SELECT (to_char(req_date,'MM/YYYY')) as monthYear, count(req_id) as count FROM REQUISITION_CURRENT t GROUP BY to_char(req_date,'MM/YYYY')
Thanks