Re: Frames vs partitions: is SQL2008 completely insane?

Hitoshi Harada wrote:
> I tested on Oracle 10.2.0, and the results are:
>
> select depname, empno, salary,
> lead(salary, 1) over (order by salary),
> lag(salary, 1) over (order by salary),
> first_value(salary) over (order by salary),
> last_value(salary) over (order by salary)
> from empsalary;
>
> DEPNAME EMPNO SALARY LEAD(SALARY,1)OVER(ORDERBYSALARY)
> LAG(SALARY,1)OVER(ORDERBYSALARY)
> FIRST_VALUE(SALARY)OVER(ORDERBYSALARY)
> LAST_VALUE(SALARY)OVER(ORDERBYSALARY)
> personnel 5 3500 3900 　　　 3500 3500
> personnel 2 3900 4200 3500 3500 3900
> develop 7 4200 4500 3900 3500 4200
> develop 9 4500 4800 4200 3500 4500
> sales 4 4800 4800 4500 3500 4800
> sales 3 4800 5000 4800 3500 4800
> sales 1 5000 5200 4800 3500 5000
> develop 10 5200 5200 5000 3500 5200
> develop 11 5200 6000 5200 3500 5200
> develop 8 6000 　　　 5200 3500 6000
>
> which means the section 4.15 is true. Could anyone try DB2?

DB2 9.5 results:

Using the empsalary table from the regression test in the patch:

select depname, empno, salary,
lead(salary, 1) over (order by salary),
lag(salary, 1) over (order by salary),
first_value(salary) over (order by salary),
last_value(salary) over (order by salary)
from empsalary;

personnel 5 3500 3900 3500 3500
personnel 2 3900 4200 3500 3500 3900
develop 7 4200 4500 3900 3500 4200
develop 9 4500 4800 4200 3500 4500
sales 4 4800 4800 4500 3500 4800
sales 3 4800 5000 4800 3500 4800
sales 1 5000 5200 4800 3500 5000
develop 10 5200 5200 5000 3500 5200
develop 11 5200 6000 5200 3500 5200
develop 8 6000 5200 3500 6000

Which matches with your Oracle results. So either they both got it wrong by
one copying the other... <sarcasm> Of course we all know it couldn't be
Oracle copying IBM, that would never happen... </sarcasm>

David