Re: How to select only the first 20 rows in a SQL statement

From: <d_a_p_at_my-deja.com>
Date: Wed, 13 Oct 1999 11:20:14 GMT
Message-ID: <7u1pt6$g2a$1@nnrp1.deja.com>

In article <38045732.306D9968_at_novasprint.com>,   Loo Kean Fui <lookf_at_novasprint.com> wrote:
> Hello
>
> Can anyone help ?
>
> I need to issue an SQL statement (either select, delete or update)
that
> I want to perform only on the first 20 rows returned and then
> subsequently repeat the statement on the next 20 rows and so on and so
> forth
>
> Is there any way I can write sucha statement?
>
> --
> L OO
> Kean Fui

With plain SQL, you can use ROWNUM to keep track of which records you are manipulating. Ex:

SELECT * FROM TABLE_NAME WHERE ROWNUM <= 20;

This will give you the first 20 rows in the table. Note however that if you use any kind of ORDER BY statement in your query, the rownum ordering will be blown. It also gets real messy to handle subsequent blocks of rows using ROWNUM.

If you really must do something to your table in discreet chunks of 20 rows (I'm curious, what are you trying to do?), the easiest thing would be to use a cursor loop in an anonymous PL/SQL block or procedure. Ex:

declare

   iloop number := 0;
   cursor c1 is

      select * from my_table;
begin

   for r1 in c1 loop

      /* Do something with the record */
      iloop = iloop + 1;
      if mod(iloop, 20) = 0 then
         /* Here is your 20th, 40th, 60th, etc. record */
      end ifl

   end loop;
end;
/

Hope this helps, but I may be able to be assist further if I had a better idea of what you are trying to do.

-Dave Pulaski
 DBD/DBA Consultant
 dave_at_dpulaski.net

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 13 1999 - 06:20:14 CDT