Hi,
I have executed the following query. I could not understand the range behavior properly. Please explain me in detail.
Output:
Please clarify the following:
1. i am confused when using rows and range in window clause.
I have demonstrated sum(sal) for 5 different ways as shown in the query and result as screen shots.
Q1:
sum(sal) over(order by sal) as cu
Here when ward and Martin has same salary then it gives 5350 instead of 4100 why? As per my understanding it evaluates with respect to current row, for Ward the salary is 1250 and it should be 4100, when i evaluate ward salary(current row) the sum of salary should come as 4100 not 5350.
Q2:
sum(sal) over( order by sal rows unbounded preceding) as cu_1
sum(sal) over ( order by sal range unbounded preceding) as cu_2
when i use rows unbounded preceding then result is coming as expected.
when i use range unbounded preceding then result is not coming as expected.
In the both the case i am summing prior salaries including current row salary.
If i use range function for the row Ward employee the sum salary is not coming properly.
Please give an explanation how range function exactly works and its behavior as compared with rows function.
The same doubt on rows and range functions used on rest of query (4,5 lines of sum(sal) lines in the query).
Thanks,