ANSI style syntax:
SELECT empno, empname, job, sal,
LAG(sal, 1, 0) RESPECT NULLS
OVER (PARTITION BY job ORDER BY empno) AS sal_prev
FROM emp
ORDER BY job, empno;
EMPNO EMPNAME JOB SAL SAL_PREV
---------- ---------- --------- ---------- ----------
12 PAUL ANALYST ? 0
13 GRACE ANALYST 3000 ?
1 JOHN CLERK 800 0
2 ERIC CLERK 950 800
3 KURT CLERK ? 950
6 JULIE CLERK 1300 ?
9 NICHOLAS MANAGER 2450 0
10 NOVAK MANAGER ? 2450
11 ROGER MANAGER 2850 ?
14 RICH PRESIDENT 5000 0
4 KENT SALESMAN 1250 0
5 LYNN SALESMAN ? 1250
7 TERESA SALESMAN 1500 ?
8 MATTHEW SALESMAN 1600 1500
Teradata style syntax:
SELECT empno, empname, job, sal,
LAG(sal RESPECT NULLS, 1, 0)
OVER (PARTITION BY job ORDER BY empno) AS sal_prev
FROM emp
ORDER BY job, empno;
EMPNO EMPNAME JOB SAL SAL_PREV
---------- ---------- --------- ---------- ----------
12 PAUL ANALYST ? 0
13 GRACE ANALYST 3000 ?
1 JOHN CLERK 800 0
2 ERIC CLERK 950 800
3 KURT CLERK ? 950
6 JULIE CLERK 1300 ?
9 NICHOLAS MANAGER 2450 0
10 NOVAK MANAGER ? 2450
11 ROGER MANAGER 2850 ?
14 RICH PRESIDENT 5000 0
4 KENT SALESMAN 1250 0
5 LYNN SALESMAN ? 1250
7 TERESA SALESMAN 1500 ?
8 MATTHEW SALESMAN 1600 1500