Re: Filtered Query on nth row

From: Donald Bowles <dbowles_at_praca.ksc.nasa.gov>
Date: 1998/02/06
Message-ID: <6bfa64$c6c@niven.ksc.nasa.gov>#1/1

Jeff Jackson wrote in message <34DA5954.3346_at_tatca.tc.faa.gov>...
>Does anyone know how to do a query and filter every nth row ? Example,
>I have 100 rows returned but I am only intereseted in every other fith
>row for example.... row1, row6, row11,row16 etc.... I don't want to see
>rows 2,3,4,5,7,8,9,10 etc ... Is this possible ?
>
>Does SQL allow me to do this ? I have tried all kinds of queries,
>correlated, inner joins, sub queries and various expressions.

You can do this with PL/SQL using a cursor.

Example:

set serveroutput on

declare
cursor c is select doc_tracking_id from doc_table; begin
for v in c loop
if c%ROWCOUNT = 1 then

DBMS_OUTPUT.PUT_LINE(v.doc_tracking_id); elsif mod(c%ROWCOUNT,5) = 1 then

DBMS_OUTPUT.PUT_LINE(v.doc_tracking_id); end if;
end loop;
end;
/

Replace the DBMS_OUTPUT.PUT_LINE calls with the processing code that you want on every 5th row.

Hope this helps.

Regards,

Don Received on Fri Feb 06 1998 - 00:00:00 CST