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

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.

Related Documents

How does one escape special characters when writing SQL queries?

Can one select a random collection of rows from a table?

How does one eliminate duplicates rows from a table?

Can one retrieve only rows X to Y from a table?

Can one retrieve only the Nth row from a table?

How can one dump/ examine the exact content of a database column?

How does one add a column to the middle of a table?

How does one select the LAST N rows from a table?

How does one select the TOP N rows from a table?

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Map/ concatenate several rows to a column

How does one code a matrix/crosstab/pivot report in SQL?