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 thoughts on “Select bottom rows”

  1. 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! :P

    
    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.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>