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.