=======================================================
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?