Help in order by

[Dhanya]

Hi,

I have a query to select months from date in a table which matches certain conditions, but result set contains month in sorted order not in the original order.

Help me to get months in original order, my query is like this,

SELECT SUBSTR(TO_CHAR(d.DOWNLOADDATE),4,6), COUNT(*) FROM DOWNLOAD d
WHERE d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
GROUP BY SUBSTR(TO_CHAR(d.DOWNLOADDATE),4,6)

[slimdave]

What do you define as the original order, because you know that there is no row ordering inherent in a relational database, right?

[TimHall]

Hi.

This is not a clever way to pull out the month from a date. It is better to use the TO_CHAR(date, 'MM') format mask, that way you are not dependent on implicit conversions to turn the date into a string.

When you convert the month to a string, it ceases to be a number, so it is sorted as ASCII text. As a result, 10 comes before 2. In order to prevent this you need to convert the resulting string back to a number:

SELECT TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'MM')) AS month, COUNT(*)
FROM   DOWNLOAD d
WHERE  d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
GROUP BY TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'MM'))
ORDER BY 1;

Of course, if the time period extends over more than a single year, you will be combining months from separate years, so you might want to include the year, to make this distinction:

SELECT TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'YYYY')) AS year,
       TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'MM')) AS month,
       COUNT(*)
FROM   DOWNLOAD d
WHERE  d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
GROUP BY TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'YYYY')), TO_NUMBER(TO_CHAR(d.DOWNLOADDATE, 'MM'))
ORDER BY 1, 2;

Cheers

Tim...

[slimdave]

TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy') is the same as SYSDATE, no?

[gamyers]

"TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy') is the same as SYSDATE, no?"
Same as TRUNC(SYSDATE)

Try the following SQL :
SELECT SUBSTR(TO_CHAR(TRUNC(d.DOWNLOADDATE,'MM')),4,6), COUNT(*) FROM DOWNLOAD d
WHERE d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
GROUP BY TRUNC(d.DOWNLOADDATE,'MM')
order by TRUNC(d.DOWNLOADDATE,'MM')

[Dhanya]

HI,

Ur query gave me the result i wanted, thanks a lot