Well, there are several issues here.

First off, the query you posted was syntactically incorrect. I made changes based on what I assumed you were trying to do.

Next, an index on a column has *nothing* to do with the order of the columns *unless* it is an INDEX ORDERED table on that index. The optimizer *might* just happen to use a plan that starts with that index and you *might* then get the rows back in the order you want. DO NOT count on this!

Next, you were converting the date to a string, *then* sorting on it. in the string, the days came first - hence your sort seemed wierd across months. Save the conversion until the end and it will work.

Next, your comparison of dates was slightly flawed. Based on the usage, I assume that the REGISTER_DATE column holds times as well, despite its name. Given that, you were almost correct with you restriction. It should be ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
, not <=, since you don't want the midnight entries from the next day, just everything up to but not including that, correct?

Last, you went to the trouble of alisasing the ROWNUM column from the inner query, then forgot to use the aliased column in the outside query. Remember that a ROWNUM exists at *every* level. Since you just said ROWNUM, and not NEWS_ROW, you were using the outside query's ROWNUM column, not the inner one that you wanted.

Here is the corrected query:

SELECT
A.STUDENT_ID ,
TO_CHAR(A.REGISTER_DATE, 'DD-MM-YYYY HH24:MI')
AS REG_DATE,
FROM
(
SELECT
ST.STUDENT_ID ,
ST.REGISTER_DATE ,
ROWNUM
AS NEWS_ROW
FROM
STUDENT ST,
SUBJECT SB
WHERE
ST.SUDENT_ID = SB.STUDENT_ID AND
SB.SUBJECT_NAME LIKE '%MATH%' AND
ST.REGISTER_DATE >= TO_DATE('01-10-2000', 'DD-MM-YYYY')
ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
/* ERROR? WITHOUT + 1 IT WILL NOT BE INCLUSIVE */
ORDER BY
REG_DATE DESC
) A ,
STUDENT ST
WHERE
ST.STUDENT_ID = A.STUDENT_ID AND
NEWS_ROW >= 1 AND
NEWS_ROW <= 20

Hope this helps,

- Chris