Oracle: I need to select n rows from every k rows of a table

Question

For example: My table has 10000 rows. First I will divide it in 5 sets of 2000(k) rows. Then from each set of 2000 rows I will select only top 100(n) rows. With this approach I am trying to scan some rows of table with a specific pattern.

there will be two parameters. Split size and scan size. There is no specific rule. It will depend on the no of rows in the table. — Raj, May 10 '17 at 08:12
It would help to know what you are trying to achieve, the table structure relevant to the range splits and perhaps some sample data. This seems like a strange business requirement so some context might help. — BriteSponge, May 10 '17 at 08:21
Sorry not to explain my problem statement correctly. @MT0 answer solved my problem. Thanks. — Raj, May 10 '17 at 08:38

score 1 · Answer 1 · answered May 10 '17 at 07:53

This solution uses the analytic ntile() to split the raw data into five buckets. That result set is labelled using the analytic row_number() which provides a filter to produce the final set:

with sq1 as ( select id, col1, ntile(5) over (order by id asc) as quintile
              from t23
            )
    , sq2 as (  select id, col1, quintile
                      , row_number() over ( partition by quintile order by id asc) as rn
                from sq1 )
select *
from sq2
where rn <= 200
order by quintile, rn
/

MT0 · Accepted Answer · 2017-05-10T11:04:30.880

Assuming you are ordering them 1 - 10000 using some logic and want to output only rows 1-100,2001-2100,4001-4100,etc then you can use the ROWNUM pseudocolumn:

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn            -- Secondly, assign a row number to the ordered rows
  FROM   (
    SELECT *
    FROM   your_table
    ORDER BY your_condition      -- First, order the data
  ) t
)
WHERE MOD( rn - 1, 2000 ) < 100; -- Finally, filter the top 100 per 2000.

Or you could use the ROW_NUMBER() analytic function:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( ORDER BY your_condition ) AS rn
  FROM   your_table
)
WHERE  MOD( rn - 1, 2000 ) < 100;

Is it possible to increase the set of sample data exponentially. Like 1k, 2k, 4k,8k....and then fetch some rows from these.

Replace the WHERE clause with:

WHERE rn - POWER(
             2,
             TRUNC( CAST( LOG( 2, CEIL( rn / 1000 ) ) AS NUMBER(20,4) ) )
           ) * 1000 + 1000 <= 100

Just to add one more case in my problem. Is it possible to increase the set of sample data exponentially. Like 1k, 2k, 4k,8k....and then fetch some rows from these. — Raj, May 10 '17 at 09:53

score -1 · Answer 3 · answered May 10 '17 at 06:53

use partition by and order by with row_number. it will look like following:

row_number()over(partition by partition_column order by order_column)<=100

partition_column will be your condition to divide set. order_column will be your condition to select top 100.

Oracle: I need to select n rows from every k rows of a table

3 Answers3

Linked