row_number ignores where clause w/ order by

select row_number() over() as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' );
order by data_size desc;



When I run the statement above to the first semicolon (without 'order by'), the row_number function works as expected. The first row is numbered 1, second is 2, etc. If I remove the semicolon and run the statement with the 'order by' clause, the first row is 55, the second is 57, the third is 137, etc. I was curious and commented out the 'where' clause and ran the statement. The 55th record without the 'where' clause was the first record in my previous results. So, the row_number ignores the 'where' clause when the 'order by' clause is used. Is this behavior normal or am I using the functions wrong?



System is at 7.3 and I'm using ACS Run Scripts.




I listed the issue in IBM Developerworks, too. Try: https://www.ibm.com/developerworks/community/forums/html/topic?id=fd1c41f9-cf56-4f2e-a633-5337270f43e8&ps=