Skip navigation.

How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

SELECT *
FROM   emp
WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                     FROM   emp);

Method 2: Use dynamic views (available from Oracle7.2):

SELECT *
FROM   ( SELECT rownum rn, empno, ename
         FROM emp
       ) temp
WHERE  MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla

SELECT rownum, f1
FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n

Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

works tweak it to Mod(rownum,2)>0) gives till 0-2

Select e.*, rowid, Mod(rownum,2) from emp e
Where rowid in (Select rowid from emp Where Mod(rownum,10)>0)

returns 9 row

select e.*, rowid, Mod(rownum,2) from emp e
Where rowid in (Select rowid from emp Where Mod(rownum,2)>0)

returns 1 row

Mod too expensive

Mod is too expensive to run per row, I'd say. It's cheaper to count up to n, pipe out a row, count up to n again, pipe out a row in a pipelined function. I'll try to post a solution later

Good one

SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);

It will be better to use this query than the others in my point of view...