Re: 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=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com