Re: row_number ignores where clause w/ order by

Charles,


I tried:


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



...and I get the same results. First record is number 55, second is 57, etc. The 'where' is still ignored as far as numbering the rows is concerned.



Patrick



------------------------------

message: 5
date: Thu, 29 Nov 2018 09:56:36 -0700
from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: row_number ignores where clause w/ order by

If you want the row_number to be generated in a specific order, you need to
include the `window-order-clause`

select row_number() over (order by data_size desc)
<snip>

Charles