Oracle: Select first # rows as ordered

Title:

Oracle: Select first # rows as ordered

Author:

Douglas O’Leary <dkoleary@olearycomputers.com>

Description:

Oracle: Select first # rows as ordered

Date created:

06/18/2009

Date updated:

06/18/2009

Disclaimer:

Standard: Use the information that follows at your own risk. If you screw up a system, don’t blame it on me…

I’ve seen this question posted on comp.databases.oracle.misc any number of times - yet until the third time through the SQL Unleashed book, didn’t realize I was staring the answer in the face - despite the paragraph heading on page 540: Selecting the First N rows as ordered, not as retrieved

OK; how to do it then. There’s basically two ways to do it:

• Create a view

• PL/SQL

Create a view method

Create a view using the group by function to get the list in order, then select rownum < #. Using the code from the SQL Unleashed:

SQL> create or replace view ordlaw as
  2  select id,
  3     rownum rowsub,
  4     name,
  5     office
  6  from lawyer1
  7  group by name, id, office, rownum;

View created.

SQL> select id, rownum, rowsub, name
  2  from ordlaw
  3  where rownum < 8;

        ID     ROWNUM     ROWSUB NAME
---------- ---------- ---------- ---------------
         8          1          8 Bonin
        11          2         11 Cardinal
        16          3         16 Chabot
        12          4         12 Chandler
        19          5         19 Chatham
         2          6          2 Cheetham
         4          7          4 Clayton

7 rows selected.

SQL>

Conversely, you could use a view by subquery as described on page 543 of the SQL UNLEASHED book - thusly:

SQL> select id, rownum, rowsub, name from
  2  ( select
  3     id,
  4     rownum rowsub,
  5     name,
  6     office
  7  from lawyer1
  8  group by name, id, office, rownum)
  9  where rownum < 8;

        ID     ROWNUM     ROWSUB NAME
---------- ---------- ---------- ---------------
         8          1          8 Bonin
        11          2         11 Cardinal
        16          3         16 Chabot
        12          4         12 Chandler
        19          5         19 Chatham
         2          6          2 Cheetham
         4          7          4 Clayton

7 rows selected.

SQL>

PL/SQL method

In a nutshell, declare a cursor as select whatever from wherever order by whatever. When fetching from the cursor, exit when ${cursor}%rowcount = ${desired_number}. To demonstrate, using the same table:

  1  declare
  2     cursor ordlaw_cur is
  3     select rownum rowsub,
  4             name, office
  5     from lawyer1
  6     order by name, office;
  7     ordlaw_rec ordlaw_cur%rowtype;
  8  begin
  9     open ordlaw_cur;
 10     loop
 11             fetch ordlaw_cur into ordlaw_rec;
 12             exit when ordlaw_cur%rowcount = 8;
 13             dbms_output.put_line(to_char(ordlaw_rec.rowsub)||
 14             ': ' || ordlaw_rec.name || ', ' || ordlaw_rec.office);
 15     end loop;
 16     close ordlaw_cur;
 17* end;
SQL> /
8: Bonin, New York
11: Cardinal, Boston
16: Chabot, New York
12: Chandler, Los Angeles
19: Chatham, New York
2: Cheetham, New York
4: Clayton, Houston

PL/SQL procedure successfully completed.

Not as pretty a layout as the normal sql method, but effective nonetheless. I’m hoping to find a way to make the plsql output a little prettier, but I’ve only just started studying…

Pretty cool, hey?