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.
»
- Log in to post comments
Comments
works tweak it to Mod(rownum,2)>0) gives till 0-2
returns 9 row
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...