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)