last_value over range between : First Last Value « Analytical Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Analytical Functions
3. First Last Value

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;


 

Related examples in the same category