Re: newbie problem with analytic function

ravi.superiors_at_gmail.com wrote:
> hi
>
> i am newbie in oracle and facing a lilttle problem please help me
>
> when i run this query it gives me correct result
>
> 1* select deptno,sal,sum(sal) over (partition by deptno order by
> empno) "sal" from e
> SQL> /
>
> DEPTNO SAL sal
> --------- ----------- ----------
> 10 2450 2450
> 5000 7450
> 1300 8750
> 20 1000 1000
> 2975 3975
> 3000 6975
> 1100 8075
> 3000 11075
> 30 1600 1600
> 1250 2850
> 3000 5850
>
> 14 rows selected.
>
> but when i dont use order by clause it gives me wrong result as follows
>
>
> 1* select deptno,sal,sum(sal) over (partition by deptno) "sal" from
> emp
> SQL> /
>
> DEPTNO SAL sal
> ---------- ---------- ----------
> 10 2450 8750
> 5000 8750
> 1300 8750
> 20 1000 11075
> 1100 11075
> 3000 11075
> 3000 11075
> 2975 11075
> 30 1600 11150
> 2850 11150
> 3000 11150
>
> what is the role of order by clause in analytic function?
>
> ravi

In general the order by within the partition exists to order the data for the purpose of calculationg the partition results. In your example the use of the order by results in the sum displayed being the cumulative sum of the salary within a department. In the second example where no order by clause exists within the partition by clause the displayed sum for all rows within a department is the department total.

You should always include the Oracle edition and version in a post since the response may need to vary depending on this.

HTH -- Mark D Powell -- Received on Wed Aug 02 2006 - 10:26:44 CDT