Re: row_number ignores where clause w/ order by

["Timothy P Clark" <timclark@xxxxxxxxxx>]

Hi Patrick,

This looks like a bug to me. Normally, I'd ask you to open a PMR with IBM 
support, but I've been able to recreate it easily here in the lab. We're 
taking a look at this and will work on getting a PTF out.

Thank you,
Tim Clark


> From: Patrick Conner via MIDRANGE-L <midrange-l@xxxxxxxxxxxx>
> To: midrange-l@xxxxxxxxxxxx, 
> Cc: Patrick Conner <pwconner@xxxxxxx>
> Date: 11/30/2018 08:17 AM
> Subject: Re: row_number ignores where clause w/ order by
> Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
> Jack,
>
>
> Thanks. Charles provided a similar statement earlier. And I get the 
> CTE works. 
>
> But I'm still curious that the row_number function works differently
> based on the 'order by' clause. Charles mentioned earlier that he 
> suspects the regExp_like is the culprit, but regExp_like is in the 
> statement that numbers only the resulting records and it is in the 
> statement that numbers the records before applying the 'where' clause.
> If someone can provide an explanation, I'm listening. Otherwise, 
> I've moved on with the CTE.
>
>
>
> Thanks to the mailing list for everyone's help
>
>
>
> -----Original Message-----
> From: Jack Callahan <jjcllhn@xxxxxxxxx>
> To: midrange-l <midrange-l@xxxxxxxxxxxx>
> Sent: Fri, Nov 30, 2018 8:41 am
> Subject: RE: row_number ignores where clause w/ order by
>
> I think this works the way you expect (row numbers assigned by 
descending
> size for the selected tables:
>
> With SelectTables AS
> (select *
> from qSys2.sysTableStat
> where regExp_like( table_schema, 'JJCWORK' )
> order by data_size desc
> )
> SELECT  row_number() over(order by data_size desc) as rowNumber,s.* from
> SelectTables s;