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.