How do I get every nth row in a table, or how do I break up a subset of a table into sets or rows of equal size?

Question

I have a table of heterogeneous pieces of data identified by a primary key (ID) and a type identifier (TYPE_ID). I would like to be able to perform a query that returns me a set of ranges for a given type broken into even page sizes. For instance, if there are 10,000 records of type '1' and I specify a page size of 1000, I want 10 pairs of numbers back representing values I can use in a BETWEEN clause in subsequent queries to query the DB 1000 records at a time.

My initial attempt was something like this

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

But this doesn't work.

Answer 1

rownum is "evaluated" when the selected records are returned, after evaluating the where clause of a select query. Therefore, you need to select on rownum in an 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 looked at NTILE? http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014248