ROWNUM AND ORDER BY

ROWNUM AND ORDER BY
We had a big issue in our production because of this interesting stupidity of us. When you query using them it looks to be very much fine, but the way you write the query does mean a lot. Normally many of us write the query like a simple example given below.

SELECT * FROM (your table name)
WHERE (your condition..)
and ROWNUM < 1000
ORDER BY date

if you query this way it normally gives the top 1000 rows of the table leaving the other rows and then sorts this 1000 rows, but in case if you first want to sort out the entire table  and then give you the top 1000 rows, here comes the confusion the above case certainly fails with the above query.
Just a few changes to query will solve your necessity.

SELECT * FROM ( SELECT *  FROM (table name)
  WHERE (your condition..)
  ORDER BY date
) WHERE ROWNUM < 1000;  


Hope it helped u.....
Cheers..
Sandeep.