Charles,
I did have a typo. Let me try this again from the beginning...
When I run this statement without an 'order by' clause, I get row number 1, 2, 3, etc. (This works!)
select row_number() over() as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' );
When I run any of these three statements with an 'order by' clause somewhere in the statement, I get row number 55, 57, 58, 91, etc.
select row_number() over() as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' )
order by data_size desc;
- OR -
select row_number() over(order by data_size desc) as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' );
- OR -
select row_number() over(order by data_size desc) as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' )
order by data_size desc;
I was expecting row_number() to work over the results by starting at 1 and increment by 1 like when the 'order by' clause was omitted. So, the numbers of 55, 57, 58, 91, etc. seemed really random and purely erroneous until I removed the 'where' clause. Now, I see where the numbers 55, 57, 58, 91, etc are generated. Row 55 is the first row when the 'where' clause is used (57 is second, etc), but instead of returning row_number() of 1, the results show 55 as if the 'where' clause was absent.
Check for yourself. The queries are over qSys2 tables.
Shouldn't row_number() work over the results of the query regardless of the 'order by' clause?
-----Original Message-----
From: Charles Wilt <charles.wilt@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thu, Nov 29, 2018 3:41 pm
Subject: Re: row_number ignores where clause w/ order by
Patrick...
Sorry, think I misread your original post...and I suspect your reply has a
typo as what you've actually posted would be a syntax error.
In answer to your actual question...
The code you posted doesn't need REGEXP_LIKE(), this will give the same
rows, but with the row_numbers() you'd expect.
select row_number() over(order by data_size desc) as rowNumber, s.*
from qSys2.sysTableStat s
where table_schema = 'CLOCF'
If you really need REGEXP_LIKE(). you'd probably want to build a CTE
with big_tbls as (
select * from qSys2.sysTableStat
where regExp_like( table_schema, 'CLOCF' )
)
select row_number() over(order by data_size desc) as rowNumber, s.*
from big_tbls;
note: huge performance improvement by using the first form :)
Now as to what's going on...
My first thought was the REGEXP_LIKE() was the only culprit...
But testing REGEXP_LIKE() on a physical table doesn't have the same issues.
Thus, the full answer seems to be that standard SQL comparisons like '=' or
'LIKE', can be pushed down into the UDTF that builds the qSys2.sysTableStat
view.
Whereas REGEXP_LIKE() can't be, so the row_number() function gets applied
first probably because the view has to be instantiated first...
Looking at the Visual Explain for both forms shows you the differences.
HTH,
Charles
On Thu, Nov 29, 2018 at 12:09 PM Patrick Conner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxx> wrote:
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
--
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
midrange.com
