Insanely Awe-dical

The application I work on selects members from a database. Sometimes we want to select a specific subset of the the rows, and we use the database variable called ROWNUM to accomplish this.

For example, if we want the top 100 members we ask the database:

SELECT MEMBERDATA FROM DATABASE
WHERE ROWNUM BETWEEN 1 and 100

This properly returns 100 people.

However, some clients want the next 100 members, so our application asks this:
SELECT MEMBERDATA FROM DATABASE
WHERE ROWNUM BETWEEN 101 and 200

However, this second query returns 0 members. Apparently the ROWNUM value doesn't represent what I think it represents. A quick google search returns the following explanation:

rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

Found at http://www.adp-gmbh.ch/ora/sql/rownum.html

This means that asking for ROWNUM BETWEEN 100 and 200 will not give me *anyone*. The first row in the result will be ROWNUM = 1, which is not between 100 and 200, and therefore NO results return. We adjust the query as so:

SELECT MEMBERDATA
FROM
(
SELECT MEMBERDATA AND ROWNUM AS ROWNUMBER
FROM DATABASE
)
WHERE ROWNUMBER BETWEEN 101 and 200.

The inner query returns all of the data and their associated rownumber (assigned the variable ROWNUMBER). Now that the number is generated, all the outer query sees is two real values - MEMBERDATA and ROWNUMBER. It is able to limit the results by that real data column, and does not fall prey to the rule associated with the psuedo-column. Now we can get the MEMBERDATA of exactly the 100 people we want to target.