WHERE MOD(ROWNUM,100) = 0

June 30, 2011

I saw an interesting search keyword the other day that generated several hits on my blog articles.  The search keyword was simply:

WHERE MOD(ROWNUM,100) = 0

Just what is interesting about the above?  Had someone seen that syntax and wondered how it worked, or was someone trying to use that syntax and found that it did not work quite as expected?

The MOD function returns the remainder value when dividing the number (ROWNUM in this case) by the specified denominator value (100 in this case).  The result of the MOD function where 100 is specified as the second parameter, with the sequential numbers 1-201 fed in as the first parameter, will appear as follows:

SELECT
  MOD(ROWNUM,100) C1
FROM
  DUAL
CONNECT BY
  LEVEL<=201;

C1
--
 1
 2
 3
 4
 5
 6
...
98
99
 0
 1
 2
 3
 4
...
98
99
 0
 1

As shown above, there is a sequence of 1-99, and then repeating  sequences of 0-99.  Note, however, that it appears the person performing the search is intending to place the MOD function in the WHERE clause, likely to retrieve every 100th row in the resultset.  The problem, of course, is this approach does not work with the ROWNUM pseudo-column.

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(ROWNUM,100) = 0;

no rows selected 

However, if we create a table and populate the table with the same 1000 rows that would be produced by the inline view found in the above SQL statement:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;  

Then, select from that table, switching out the ROWNUM keyword with the C1 column found in the table T1:

SELECT
  *
FROM
  T1
WHERE
  MOD(C1,100) = 0;

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

We see that 10 rows were returned – every 100th row.  At this point, you might be wondering if the following will work:

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(C1,100) = 0; 

Here is the output of the above SQL statement:

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

So, the query works as expected when we use the alias of the ROWNUM column from the inline view when the MOD function appears in the WHERE clause, but the query does not work as (probably) intended when the ROWNUM pseudo-column is used directly in the MOD function in the WHERE clause.  Why?  Think about when the ROWNUM value is assigned – not until after the WHERE clause is applied.  See the comments attached in this related blog article.