Today I answered a question on otn regarding order by.
Let’s build a different test case.
You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column.
SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A; Grant succeeded. SYS@LSC01> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION; Grant succeeded. SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP ORDER BY HIREDATE; View created. SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B; Grant succeeded. SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B; Grant succeeded. SYS@LSC01> CONNECT USER_B/B Connected. USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4; ROWNUM ENAME ---------- ---------- 1 SMITH 2 ALLEN 3 WARD
To get top 3, that was easy 8)
USER_B@LSC01> SELECT R, ENAME FROM 2 (SELECT ROWNUM R,ENAME 3 FROM USER_A.V 4 ORDER BY ROWNUM DESC) 5 WHERE ROWNUM<4 ORDER BY R; R ENAME ---------- ---------- 12 MILLER 13 SCOTT 14 ADAMS
Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key