Oracle rownum behavior with mod function


Given

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);

and

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.


Answers:


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.