Hi Vern,
@1: Yes SQL does not guarantee order ... because without additional ORDER BY the sequence of the index (or sequence of the rows - for a table scan) that is used is returned
@2/@3: Partition By handles a level break, i.e. it start renumbering on each new level.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Dienstag, 6. Oktober 2020 22:40
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: using OVER(ORDER BY) without PARTITION BY - possible issues?
Hi again
Birgitta helped me with my issue earlier with this expression -
ROW_NUMBER() OVER(ORDER BY refcod, change_ts) rownum -
I needed to use PARTITION BY.
Now I was trying to figure out what it has been working for over 2 years, and I think I know maybe why - SQL does not guarantee the order of the returned rows.
OK, I think I have an answer -
1. SQL doesn't guarantee order, so even RRN might not be honored
2. If when using ROW_NUMBER OVER(ORDER BY REFCOD, CHANGE_TS) I don't specify the PARTITION BY, SQL thinks the entire result set is the
a. Then the order of that partition could be different from run to run
3. If using PARTITION BY REFCOD, then there is a set for each REFCOD and there is a row number for each row there, based on order of CHANGE_TS
I just checked, and the table in question has REUSEDLT(*YES) - hence, the order without partitioning is topsy-turvy!!
Does that make sense?
Lesson learned, I think.
Cheers
Vern
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
