Topic
  • 1 reply
  • Latest Post - ‏2010-06-24T23:30:36Z by NDG
Java_Learner
Java_Learner
1 Post

Pinned topic Ordering the retrieved dates from Database

‏2010-06-19T19:57:04Z |
I am retrieving the data from Oracle using JDBC API. I want to get the last 24 months starting from current month and the respective count of records.
I wrote the query as shown below:
(TIMESTAMP is of Data type DATE)

SELECT MONTH_YEAR,SUM(MONTH_COUNT) FROM
(SELECT
REPLACE(TO_CHAR(TIMESTAMP,'MON-YYYY'),'-','_') AS MONTH_YEAR, 1 AS MONTH_COUNT
FROM
MASTER
WHERE
TIMESTAMP >= TRUNC((ADD_MONTHS(SYSDATE, -24)),'MM'))
GROUP BY MONTH_YEAR

However, i cannot order by MONTH_YEAR as that will be ordered by alphabets(A,B,C..) as MONTH_YEAR is a string.
What i would appreciate is if anyone could point me how to order the result by date either on the DB query or on the Java end.

Thanks in advance!
Updated on 2010-06-24T23:30:36Z at 2010-06-24T23:30:36Z by NDG
  • NDG
    NDG
    2 Posts

    Re: Ordering the retrieved dates from Database

    ‏2010-06-24T23:30:36Z  
    how about ordering it first before grouping it?
    CREATE TABLE TEMPTABLE AS
    (SELECT TIMESTAMP, 1 AS MONTH_COUNT
    FROM MASTER
    WHERE TIMESTAMP TIMESTAMP >= TRUNC((ADD_MONTHS(SYSDATE, -24)),'MM'))
    ORDER BY TIMESTAMP DESC)

    SELECT MONTH_YEAR, SUM(MONTH_COUNT)
    FROM TEMPTABLE
    GROUP BY MONTH_YEAR