How does one select EVERY Nth row from a table?

Submitted by admin on Wed, 2005-12-14 23:36
Body: 

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.

»

Comments

Submitted by SNiper (not verified) on Sat, 2006-10-28 14:34. 

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

»

Submitted by coderextreme on Fri, 2011-08-19 13:52.

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

»

Submitted by thanmathpradeep... on Sat, 2012-02-18 04:06.

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...

»