Partition By Clause is giving different results due to ORDER BY clause

Question

I am trying to run a partition query on a sample data.It's giving me different results if I remove ORDER BY clause.What is the reason for different results.Please check the below link.

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

@Vutukuri - I did, I realize those queries are different, but on your fiddle they show the same **results** — Lamak, Apr 25 '12 at 17:17
I am surprised.. Its showing me different results on my fiddle(the link I posted).. — Teja, Apr 25 '12 at 17:19
@Vutukuri:Analytic functions are computed after all joins, WHERE clause, GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query operates after the analytic functions. So analytic functions can only appear in the select list and in the main ORDER BY clause of the query,but if you want to specify the order or records in partition you need to give the order by clause in analytic function . — Gaurav Soni, Apr 25 '12 at 17:22
@Lamak See this http://www.sqlfiddle.com/#!4/a0f10/6 - the first record in the first query is 11:05; the rest of the records are 11:50 — Jake Feasel, Apr 25 '12 at 17:23
@JakeFeasel - I see the difference now, but I don't really have an explanation for this behavior — Lamak, Apr 25 '12 at 17:33

score 4 · Accepted Answer · edited May 23 '17 at 12:13

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

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

EDIT

Referring to the sqlfiddle here:

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

The first query:

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;

... returns

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

So let's break this down... The first row is a distinct ST so the MAX up to that point is whatever is in that row. Next row, the ST is not unique. So the MAX for all rows up to that point is not 11:15, but rather 11:50 for BOTH rows with ST 11:03. The final two rows never have ET after 11:50, so that's what we show for all remaining rows. We've used the ORDER BY in the OVER clause, so that's what we told Oracle to do.

As arturro points out, the default range on the analytical function will be the row in question through all previous rows based on the sort in the ORDER BY. If you add RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, you should get the same results as without the ORDER BY. — JAQFrost, Apr 25 '12 at 23:06

score 2 · Answer 2 · answered Apr 25 '12 at 18:31

Oracle documentation for analytic functions says:

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

If you understand how RANGE works it should be clear why you are seeing such results.

Partition By Clause is giving different results due to ORDER BY clause

2 Answers2