How to get every nth row in a table or how to split a subset of a table into groups or rows of the same size?

Question

I have a table of disparate pieces of data identified by a primary key (ID) and type identifier (TYPE_ID). I would like to be able to fulfill a query that returns me a set of ranges for a given type, broken down into even page sizes. For example, if there are 10,000 records of type "1", and I specify a page size of 1000, I want 10 pairs of numbers to return values ​​that I can use in the sentence BETWEENin subsequent queries to query DB 1000 records in time.

My initial attempt was something like this

select id, rownum from CONTENT_TABLE 
where type_id = ? and mod(rownum, ?) = 0

But that does not work.

Answer 1

rownum"evaluated" when the selected records are returned, after evaluating the where clauseselected query. So you need to select in rownum in another query. I give r as an alias for rownum:

select id from (
  select 
    id, 
    rownum r
  from 
    CONTENT_TABLE 
   where type_id = ? 
)
where mod(r, ?) = 0

Answer 2

Have you watched NTILE? http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014248

Answer 3

Hey. Use below SQL to find every nth row in a table:

SQL query:

SELECT * 
FROM table_name
WHERE (ROWID,0) in (SELECT ROWID, MOD(ROWNUM, n)
FROM table_name);
Replace n with 2, 3, 4 , 5 ...