Re: A SQL quiz, tough one

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/04/19
Message-ID: <335a3c57.4555267_at_netnews.worldnet.att.net>#1/1

On 19 Apr 1997 00:08:27 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:

>as you can see, I marked two lines off because 'ROWNUM <= 10' clause generates a wrong data set
>and 'ORDER by' clause is not legal in a INSERT.
>So, any smart solutions for this?

I don't think you will be able to use rownum to help you here. I'm 99% certain that it gets set when a row is first retrieved from the database, not after all the ordering and grouping is done. So the rownum value has nothing to do with the top 10, or bottom 10.

I think you will have to break down and write some procedural code, maybe using pl/sql.

Maybe something like this:

SQL> create or replace procedure get_top_ten as   2 cursor c1 is select * from my_table order by fld_pk desc;
  3 inx1 integer;
  4 c1_rec c1%rowtype;
  5 begin
  6 dbms_output.put_line('The highest two primary key numbers are:');
  7 open c1;
  8 for inx1 in 1..2 loop

  9      fetch c1 into c1_rec;
 10      exit when c1%notfound;
 11      dbms_output.put('     ');
 12      dbms_output.put_line(c1_rec.fld_pk);

 13 end loop;
 14
 14 close c1;
 15 end;
 16 /

Procedure created.

SQL> execute get_top_ten;

PL/SQL procedure successfully completed.

SQL> set serveroutputon
unknown SET option beginning "serveroutp..." SQL> set serveroutput on
SQL> execute get_top_ten;
The highest two primary key numbers are: 3
2

PL/SQL procedure successfully completed.

SQL> commit;

Jonathan

---

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Sat Apr 19 1997 - 00:00:00 CEST