Select bottom rows

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

2 Comments

  • 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;

Leave a Reply

Your email address will not be published.