last_value over range between
create table employee(
emp_no integer primary key
,lastname varchar2(20) not null
,hiredate date
,salary integer
);
insert into employee(emp_no, lastname, hiredate,salary)
values (1,'Nancy','1-feb-1999',12);
insert into employee(emp_no, lastname, hiredate,salary)
values (2,'Joey','1-feb-2000',13);
insert into employee(emp_no, lastname, hiredate,salary)
values (3,'Goldy','1-feb-2001',14);
insert into employee(emp_no, lastname, hiredate,salary)
values (4,'Bluey','1-feb-2002',15);
select lastname , hiredate, salary,
avg(salary) over (order by hiredate
range between 182 preceding and 182 following) avg_sal,
last_value(lastname) over (order by hiredate
range between 182 preceding and 182 following) window_bottom
from employee
order by hiredate asc;
LASTNAME HIREDATE SALARY AVG_SAL WINDOW_BOTTOM
-------------------- --------- ---------- ---------- --------------------
Nancy 01-FEB-99 12 12 Nancy
Joey 01-FEB-00 13 13 Joey
Goldy 01-FEB-01 14 14 Goldy
Bluey 01-FEB-02 15 15 Bluey
drop table employee;