This code snippet is an extension of my earlier code snip How to select the records from a table following a pattern. There I used ROWNUM to find the even/odd records from the emp table. Current code snip achieves the same functionality even without the use of ROWNUM. This method proves useful when it is asked to avoid the use of ROWNUM.

Oracle 9i or above versions

Listing 1

select * from emp where MOD(ASCII(SUBSTR(ROWID,LENGTH(ROWID))),2)=1;

Explanation

In one of my earlier code snips I used the ROWNUM to manipulate the record number mathematically in order to get the odd number of records as given in Listing 2.

Listing 2

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

But without using rownum it becomes difficult to manipulate the record number mathematically. In this situation, the approach mentioned in Listing 1 proves handy. The code uses a key word, like rownum, and many oracle functions, like mod(), ascii(), substr() and length(). Oracle points each record in a table through a unique pattern consisting of only alphabets called ROWID. In order to use ROWID's mathematically, we need to interpret the ROWID's of each row mathematically.

Interpretation can be understood from the snapshot beloweasily.

Figure 1

Let us dig deep into the query in Listing 1.

First the length() function captures the length of ROWID assigned for each record which returns the maximum character length for each ROWID.

Then use of the SUBSTR(ROWID,LENGTH(ROWID)) function can peak the right most alphabet of each ROWID which uniquely represents each record in the EMP table as shown by Figure 1.

Now by using ASCII(SUBSTR(ROWID,LENGTH(ROWID))) we can interpret each ROWID mathematically to manipulate in any useful form.

Finally, to extract the odd number of records we have to peak the ROWID that upon dividing by 2 leaves the reminder as 1.

That can be easily done by MOD(ASCII(SUBSTR(ROWID,LENGTH(ROWID))),2)=1.

Figure 2: Printing Odd numbered rows by using Listing 1

The above approach explains how the ROWID can be manipulated mathematically to get odd/even records from a table.