Oracle rownum behavior with mod function
CREATE TABLE Foo(v1 integer, v2 integer); INSERT INTO Foo VALUES (1,2); INSERT INTO Foo VALUES (3,4); INSERT INTO Foo VALUES (5,6);
SELECT COUNT(*) FROM Foo WHERE mod(rownum,2) = 0;
returns 0 rows, where it should return 1
SELECT COUNT(*) FROM (SELECT mod(rownum,2) z FROM Foo) tt WHERE z=0;
returns 1 rows
What am I missing here? I don't see anything in the ROWNUM or MOD docs suggesting any ideas.
ROWNUM returns the row number of the actual query, not of the table the query is base on. So
SELECT COUNT(*) ... returns one row with rownumber 1 with
mod(rownum,2) != 0, which is then discarded by the WHERE caluse, effectively returning no rows at all.
A (unfiltered) subselect returning a rownumber as column can help. The WHERE clause is then applied to the outer select.