Tuesday, June 3, 2008

SQL Query to find every Nth row from a table?


Question: SQL Query to find every Nth row from a table?


Answer: There are many ways of getting it. One possible way is to use a subsequery, which uses the MOD function on ROWID and the result of the subquery is used by the external query to find every Nth row. The query will be something like:-


SELECT * FROM TABLE_NAMEWHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME);


Here the subquery 'SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME' will have the resultset having records of the form:-

(ROWID, 0)

(ROWID, 1)

(ROWID, 2)

............

(ROWID, N-1)


Now, the external query will pick only those records for which the subquery return (ROWID, 0) and these records will obviously be every Nth record as the MOD function will return (ROWID, 0) in all such cases.



Share/Save/Bookmark


1 comment:

phoenixric said...

This one is very useful. Thanks