Rownum

ROWNUM is the order in which a row is returned to the result set after all predicates (WHERE clause) are applied. It can be affected by implicit sorts (i.e. DISTINCT) and access paths (i.e. INDEX v. TABLE). An ORDER BY will preserve the rownum, but will not display it in sequential order.

There are several conditions where a predicate with ROWNUM will always evaluate to FALSE.

1. ROWNUM >|>= n (rownum greater|greater than or equal to than any number)
2. ROWNUM = 0 (rownum can never be 0)
3. ROWNUM = 2...(rownum can only be assigned to a row that matches the predicate. The first row returned is assigned 1 temporarily and the predicate is checked. Since 1 != 2, it fails and the next row read is assigned rownum of 1 temporarily.)

I have a discussion of ROWNUM, its uses and limitations on my website at www.optimaldba.com/library.html. The Paper/Presentation are SQL Scripting Sorcery.

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals