This discussion is archived
4 Replies Latest reply: Apr 1, 2013 10:42 AM by mbb774 RSS

order by 'mon-yyyy' desc

mbb774 Newbie
Currently Being Moderated
Hi experts,

i wrote a query like below.

SQL> SELECT mon."months",
2 COUNT (DECODE (e1.ename,
3 'RAVI', 1,
4 DECODE (e1.ename, 'KIRAN', 1, NULL)
5 )
6 ) AS "num-review"
7 FROM (SELECT TO_CHAR (ADD_MONTHS (SYSDATE, LEVEL - 7),
8 'MON-YYYY'
9 ) "months"
10 FROM DUAL
11 CONNECT BY LEVEL <= 18
12 ORDER BY LEVEL) mon,
13 (SELECT ename, hiredate, TO_CHAR (hiredate, 'MON-YYYY') "Month"
14 FROM emp_copy) e1
15 WHERE mon."months" = e1."Month"(+)
16 GROUP BY "months";

months num-review
-------- ----------
APR-2013 0
AUG-2013 0
DEC-2012 1
DEC-2013 0
FEB-2013 2
FEB-2014 0
JAN-2013 1
JAN-2014 0
JUL-2013 0
JUN-2013 0
MAR-2013 0

months num-review
-------- ----------
MAY-2013 0
NOV-2012 0
NOV-2013 0
OCT-2012 1
OCT-2013 0
SEP-2012 1
SEP-2013 0

18 rows selected.

here i am getting a problem on months column, i supposed to get output like

Sep-2012
Oct-2012
Nov-2012
Dec-2012
Jan-2013
Feb-2013
Mar-2013
Apr-2013
May-2013
Jun-2013
Jul-2013
Aug-2013
Sep-2013
Oct-2013
Nov-2013
Dec-2013
Jan-2014
Feb-2014

can you guys please help in getting the output like above..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points