Datacentre Support Reference Guides
Visit our online shop for ALL your parts, components,
laptops, electronics and gadgets Oracle: Select first # rows as ordered | |
| 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 methodCreate 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 methodIn 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?
| |