## WHERE MOD(ROWNUM,100) = 0

30 06 2011

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.