Ordering the retrieved dates from Database

[Piyush Mattoo]

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!

[Andrew Monkhouse]

I tried putting your SQL inside code blocks, but it still was hard for me to read. This is possibly just a question of style (and I have yet to find a good style for SQL that everyone agrees to). I think your code can be rewritten as:

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

My first question is: why do you need the sub-clause?

Within your sub-clause, you have access to the timestamp, which I assume is a timestamp. If so, you could order it according to the timestamp itself.

[Andrew Monkhouse]

I don't have access to an Oracle database right now, but I would have thought that this could be simplified:

        REPLACE(TO_CHAR(TIMESTAMP,'MON-YYYY'),'-','_') AS MONTH_YEAR, 

The to_char function converts the date field according to your format specification. So specifying that the conversion should use a dash, then later converting the dash into an underscore seems redundant - couldn't you just specify the underscore in the to_char format definition?

[Ireneusz Kordal]

Hi,

use EXCTRACT conversion function, it extracts months and years as numbers,
it is available starting from Oracle 9i:

SELECT LPAD(MONTH,2,'0') || '_' || YEAR MONTH_YEAR, COUNT(*)
FROM (
    SELECT  EXTRACT(YEAR FROM "TIMESTAMP") YEAR, 
            EXTRACT(MONTH FROM "TIMESTAMP") MONTH
    FROM MASTER
    WHERE "TIMESTAMP" >= TRUNC((ADD_MONTHS(SYSDATE, -24)), 'MM') 
)
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

Also avoid using TIMESTAMP as a column name, TIMESTAMP is a data type,
it could cause confusion and Oracle can give you strange syntax errors in some cases.
But if you must, enclose it in brackets - like this- "TIMESTAMP".

[Piyush Mattoo]

@Andrew: I would imagine i cannot order by timestamp though i can by MONTH_YEAR which would give me the
alphabet ordering.

You are right, No need of replace function

@Ireneusz: Point noted. This does work. Only thing is that for past 24 months, we need to subtract 23 months from sysdate.
WHERE "TIMESTAMP" >= TRUNC((ADD_MONTHS(SYSDATE, -23)), 'MM')

Thankyou for your valuable opinions.