Re: SQL problem. HELP!

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/06/17
Message-ID: <33A7007C.E17@iol.ie>#1/1

Arnold Jones wrote:
>
> Jerry,
> The traditional way to find the highest NN rows in a table is:
>
> select col1,col2 from tab1 t where NN >= (select count(*) from tab1 tt
> where t.col2 < tt.col2);
>
> Regards,
> AJ.
>
> Jerry Glass wrote:
> >
> > (snip)

Why do people use this incredibly inefficient mechanism? This requires a full table scan of tt for each row of tt! Yet it keeps recurring in this NG.

The problem is that SQL*Plus does not give you direct access over the number of rows fetched (as opposed to defined), except for the rownum mechanism (which is no use for sorted sets). So why use SQL*Plus?

The *only* efficient way to do this is to use a single scan of the table. This can be done in a PL/SQL procedure (or a 3GL program) thus:

declare

   cursor C is

       select ...
        from  ...
       order by ...;

   ctr number := 1;
begin

   for R in C
   loop

      ctr := ctr+1;
      if ctr > nn then	-- required number of rows processed
         exit;
      else
         <process/output current row>
      end if;

   end loop;
end;

Chrysalis. Received on Tue Jun 17 1997 - 00:00:00 CDT