Geek asks and answers

Section Section gives different results due to ORDER BY clause

I am trying to run a section query on sample data. It gives me different results if I delete the ORDER BY clause. What is the reason for the different results. Please check the link below.

http://www.sqlfiddle.com/#!4/a0f10/5

SELECT MAX(B1.ET) OVER(PARTITION BY B1.MAS_DIV_KEY,B1.STN_KEY,B1.SBSC_GUID_KEY ORDER BY B1.ST)AS TEST_COL
FROM AM_PROGRAM_TUNING_EVENT_TMP1 B1;


SELECT MAX(B1.ET) OVER(PARTITION BY B1.MAS_DIV_KEY,B1.STN_KEY,B1.SBSC_GUID_KEY)AS TEST_COL1
FROM AM_PROGRAM_TUNING_EVENT_TMP1 B1;

enter image description here

enter image description here

+3
source share
2 answers

When you have an order inside the OVER () clause for aggregations that are usually not sensitive to sorting (min, max, stdev, etc.), it becomes an intermediate subtotal. See Justin Cave's answer to the question below for a more thorough treatment.

Oracle MIN as an analytic function - odd behavior with ORDER BY?

EDIT

Referring to sqlfiddle here:

http://www.sqlfiddle.com/#!4/a0f10/33

:

SELECT B1.ST
  , B1.ET
  , MAX(B1.ET) 
    OVER(PARTITION BY B1.MAS_DIV_KEY
             ,B1.STN_KEY
             ,B1.SBSC_GUID_KEY 
         ORDER BY B1.ST
         ) AS TEST_COL
FROM AM_PROGRAM_TUNING_EVENT_TMP1 B1
ORDER BY B1.ST;

...

ST                            ET                            TEST_COL
March, 28 2012 11:00:00-0700  March, 28 2012 11:05:00-0700  March, 28 2012 11:05:00-0700
March, 28 2012 11:03:00-0700  March, 28 2012 11:15:00-0700  March, 28 2012 11:50:00-0700
March, 28 2012 11:03:00-0700  March, 28 2012 11:50:00-0700  March, 28 2012 11:50:00-0700
March, 28 2012 11:10:00-0700  March, 28 2012 11:30:00-0700  March, 28 2012 11:50:00-0700
March, 28 2012 11:20:00-0700  March, 28 2012 11:50:00-0700  March, 28 2012 11:50:00-0700

, ... - ST, MAX - , . , ST . , MAX 11:15, 11:50 BOTH ST 11:03. ET 11:50, . ORDER BY OVER, , Oracle.

+4

Oracle :

*If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.*

, RANGE, , .

+2

More articles:


All Articles