FIRST_VALUE and LAST_VALUE function

The general syntax is:

FIRST_VALUE(<sql_expr>) OVER (<analytic_clause>)

The FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY. The <sql_expr> is computed on the columns of this first record and results are returned. The LAST_VALUE function is used in similar context except that it acts on the last record of the partition.

-- How many days after the first hire of each department were the next

-- employees hired?

SELECT empno, deptno, hiredate, 

FIRST_VALUE(hiredate)

OVER (PARTITION BY deptno ORDER BY hiredate) F_VALUE,

hiredate-FIRST_VALUE(hiredate)

OVER (PARTITION BY deptno ORDER BY hiredate)  DAY_GAP

FROM SCOTT.emp

WHERE deptno IN (10,20, 30)

ORDER BY deptno, DAY_GAP;

EMPNO	DEPTNO	HIREDATE	F_VALUE	DAY_GAP
7782	10	6/9/1981	6/9/1981	0
7839	10	11/17/1981	6/9/1981	161
7934	10	1/23/1982	6/9/1981	228
1001	10	3/8/2010	6/9/1981	10499
7369	20	12/17/1980	12/17/1980	0
7566	20	4/2/1981	12/17/1980	106
7902	20	12/3/1981	12/17/1980	351
7788	20	12/9/1982	12/17/1980	722
7876	20	1/12/1983	12/17/1980	756
1002	20	3/1/2012	12/17/1980	11397
7499	30	2/20/1981	2/20/1981	0
7521	30	2/22/1981	2/20/1981	2
7698	30	5/1/1981	2/20/1981	70
7844	30	9/8/1981	2/20/1981	200
7654	30	9/28/1981	2/20/1981	220
7900	30	12/3/1981	2/20/1981	286

Query (FIRST_VALUE)