Oracle date / order by question

Question

I want to select a date from an oracle table formatted as select (to_char(req_date,'MM/YYYY')), but I also want to order a result set of this date format.

I want to be ordered as 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

I do 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:

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')

thank

Answer 1

try it

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

Answer 2

Try it. It works and is effective, but it looks a bit dirty.

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');

Answer 3

Select req_date, (to_char(req_date,'MM/YYYY')) from MY_TABLE order by req_date

, .

Answer 4

order by req_date order by to_char(req_date,'MM/YYYY').

Answer 5

Try

SELECT ... 
ORDER BY MIN(req_date)

Oracle , GROUP BY.

Answer 6

I'm very late to the party, but the most intuitive way I've found to do this is this:

SELECT DISTINCT
   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')
ORDER BY 
   to_date(monthYear,'MM/YYYY')

This may not be the most computationally efficient method since it converts the date to a character and then returns to the date, but this is exactly what you are asking for such a request. It also saves you from adding support columns or nested subqueries.