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.
Did you know there is a whole other DB2 for i forum here in IBM Community? I posted this issue yesterday at Forum Directory > Communities category > Forum: DB2 for i > Topic: row_number ignores where clause w/ order by