Re: SQL:Getting the 'Nth' records from a table..

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 24 Sep 1999 16:02:43 -0400
Message-ID: <CMrrN0dgra7I=pAnh=WCjJb3+Wmi@4ax.com>

On Fri, 24 Sep 1999 18:02:53 GMT, narayana_ck_at_my-deja.com wrote:

>I need to write a SQL query which can get the records from a table with
>a frequency of 'n'.
>
>Retrieve the 1st, 5th, 10th, 15th, 20th, 25th...
>rows from a table.
>
>Is there a way to get it straight from SQL??

Yes but you first need to define an 'pseudo ordering' for the rows since there is no 'order' of rows in a relational database table. Is it date ordered, seqno ordered, alphabetical, ...?

The example below assumes that the table will be 'ordered' via the ts ( date ) column.

eg.

SQL> desc t

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 TS                                     DATE

SQL> select * from t;

ID TS

---------- ---------
         1 24-SEP-99
         2 25-SEP-99
         3 26-SEP-99
         4 27-SEP-99
         5 28-SEP-99
         6 29-SEP-99
[snip]
        99 31-DEC-99
       100 01-JAN-00

100 rows selected.

Every 5th record...

SQL>
  1 select a.id, a.ts
  2 from t a, t b
  3 where b.ts <= a.ts

  4     and ( b.ts < a.ts OR
  5           b.rowid < a.rowid )

  6 group by a.id, a.ts
  7* having mod( count(*)+1, 5 ) = 0
SQL> /         ID TS

---------- ---------
         5 28-SEP-99
        10 03-OCT-99
        15 08-OCT-99
        20 13-OCT-99
        25 18-OCT-99
[snip]
        95 27-DEC-99
       100 01-JAN-00

20 rows selected.

Every 15th record...

SQL>
  1 select a.id, a.ts
  2 from t a, t b
  3 where b.ts <= a.ts

  4     and ( b.ts < a.ts OR
  5           b.rowid < a.rowid )

6 group by a.id, a.ts
7* having mod( count(*)+1, 15 ) = 0

ID TS

---------- ---------
        15 08-OCT-99
        30 23-OCT-99
        45 07-NOV-99
        60 22-NOV-99
        75 07-DEC-99
        90 22-DEC-99

6 rows selected.

hope this helps.

chris.

>
>thanks
>ck
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 24 1999 - 15:02:43 CDT