This code snip explains how to select only those records from a table which follow a particular series. Let us say we want to find only the odd numbered rows from a table.

Oracle 9i or above versions

Let us first examine the SQL code given below.

Listing 1

select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

Explanation

The code uses two crucial key words, like rownum and rowid, and a system function like mod(). Here the inner query associates each rowid with either 0 or 1. This is because when rownum (essentially takes positive integers starting from 1) of each record is divided by 2 using mod() function there can only be two possible remainders: 0 or 1. In case of 0 as the remainder, the rownum represents an even numbered record and in case of 1 as the reminder, the rownum points to an odd numbered row.

Since we require the odd series, the outer query selects only those records whose rowid is associated with 1.

Structures of emp table

Figure 1

It is showing all the records of emp table up to row 25. Now we are required to show only the odd numbered records showing FIRST_NAME, like Steven, Lex, Bruce, Valli, etc.

Listing 2

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME 
from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

Figure 2

The use of this query can be extended many ways by manipulating the reminder (used in the where clause) and divisor (used in the mod() function).