| Analytic Function [message #625630] |
Sun, 12 October 2014 00:28  |
 |
kiwinz
Messages: 13
Registered: September 2014
Location: WELLINGTON
|
Junior Member |
|
|
Hi,
I have a query for using order by in partition by clause for using a MAX,MIN function .
please suggest whether order by makes a difference for using functions like MAX,MIN,AVG,. In my opinion.
It will not make any difference as the calculation will be done based on a particular group irrespective of order by on any column
please suggest if this understanding is wrong ????
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Analytic Function [message #625641 is a reply to message #625635] |
Sun, 12 October 2014 02:38  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Analytic functions, without any windowing clause, is a rolling function with default window clause as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which mean it applies the analytic function to the rows before the first one (in the order you specify) and all the rows having the same value than the current row.
So the order matter. For instance:
SQL> select empno, sum(sal) over(order by sal) sumsalsal, sum(sal) over(order by empno) sumsalemp
2 from emp order by empno;
EMPNO SUMSALSAL SUMSALEMP
---------- ---------- ----------
7369 800 800
7499 9750 2400
7521 5350 3650
7566 18025 6625
7654 5350 7875
7698 15050 10725
7782 12200 13175
7788 24025 16175
7839 29025 21175
7844 8150 22675
7876 2850 23775
7900 1750 24725
7902 24025 27725
7934 6650 29025
If you omit ORDER BY clause than the function is applied to the whole partition:
SQL> select deptno, empno,
2 sum(sal) over(partition by deptno) sumsaldept,
3 sum(sal) over() sumsalall
4 from emp
5 order by deptno, empno
6 /
DEPTNO EMPNO SUMSALDEPT SUMSALALL
---------- ---------- ---------- ----------
10 7782 8750 29025
10 7839 8750 29025
10 7934 8750 29025
20 7369 10875 29025
20 7566 10875 29025
20 7788 10875 29025
20 7876 10875 29025
20 7902 10875 29025
30 7499 9400 29025
30 7521 9400 29025
30 7654 9400 29025
30 7698 9400 29025
30 7844 9400 29025
30 7900 9400 29025
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
