Re: row_number ignores where clause w/ order by

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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

On Thu, Nov 29, 2018 at 9:51 AM Patrick Conner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxx> wrote:

>       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