select * from emp where MOD(ASCII(SUBSTR(ROWID,LENGTH(ROWID))),2)=1;
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.
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.
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
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
Figure 2: Printing Odd
numbered rows by using Listing 1