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
Laurent, i find SELECTing by Rownum and ORDERing BY it in the same statement, to be confusing. Probably just a familiarity thing.
I tried you example in a slightly different way. The user thing was too much effort! 😛
WITH
Moo
AS
(
SELECT 5 A, 0 B FROM Dual UNION ALL
SELECT 6 A, 1 B FROM Dual UNION ALL
SELECT 4 A, 2 B FROM Dual UNION ALL
SELECT 7 A, 3 B FROM Dual UNION ALL
SELECT 3 A, 4 B FROM Dual UNION ALL
SELECT 8 A, 5 B FROM Dual UNION ALL
SELECT 2 A, 6 B FROM Dual UNION ALL
SELECT 9 A, 7 B FROM Dual UNION ALL
SELECT 1 A, 8 B FROM Dual UNION ALL
SELECT 0 A, 9 B FROM Dual
),
Cow
AS
(
SELECT
A
FROM
Moo
ORDER BY
B
)
SELECT
R,
A
FROM
(
SELECT
RowNum R,
A
FROM
Cow
ORDER BY
Rownum DESC
)
WHERE
Rownum< 4 ORDER BY R;
of course you are right, thanks for your feedback. The “user” thing was supposed to be a business justification
glad you tried with WITH 🙂