Laurent Schneider

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


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

This entry was posted on Thursday, May 1st, 2008 at 11:49 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

2 Responses to “Select bottom rows”

Brian Tkatch Says:
May 1st, 2008 at 14:29

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;

Laurent Schneider Says:
May 1st, 2008 at 16:13
of course you are right, thanks for your feedback. The “user” thing was supposed to be a business justification

glad you tried with WITH

Use <code> and </code> to post code

Select bottom rows

2 Responses to “Select bottom rows”

Leave a Reply