LogoASPAlliance: Articles, reviews, and samples for .NET Developers
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19930/ 86


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



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



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).

©Copyright 1998-2020  |  Page Processed at 2020-07-14 1:28:42 PM  AspAlliance Recent Articles RSS Feed