Think it will, but I don't have an Oracle db on-site here to try it out (it's all off the top of my head, so caveats apply).
Originally posted by anil bisht:
first of all for "rownum between n and m " query will not work. (correct me if i am wrong)
Spot on, I think - logically, the sorting is done after applying the WHERE clause. So you'd need to junk your order by and use indexes strategically to get the sort order right - not very nice and not always possible. Anyway, the ANSI SQL method should stil work.
and second case the order by works only on the selected rows not on all.