ANSIスタイルの構文：
SELECT empno, empname, job, sal,
LAG(sal, 1, 0) IGNORE 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 0
1 JOHN CLERK 800 0
2 ERIC CLERK 950 800
3 KURT CLERK ? 950
6 JULIE CLERK 1300 950
9 NICHOLAS MANAGER 2450 0
10 NOVAK MANAGER ? 2450
11 ROGER MANAGER 2850 2450
14 RICH PRESIDENT 5000 0
4 KENT SALESMAN 1250 0
5 LYNN SALESMAN ? 1250
7 TERESA SALESMAN 1500 1250
8 MATTHEW SALESMAN 1600 1500
Teradataスタイルの構文：
SELECT empno, empname, job, sal,
LAG(sal IGNORE 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 0
1 JOHN CLERK 800 0
2 ERIC CLERK 950 800
3 KURT CLERK ? 950
6 JULIE CLERK 1300 950
9 NICHOLAS MANAGER 2450 0
10 NOVAK MANAGER ? 2450
11 ROGER MANAGER 2850 2450
14 RICH PRESIDENT 5000 0
4 KENT SALESMAN 1250 0
5 LYNN SALESMAN ? 1250
7 TERESA SALESMAN 1500 1250
8 MATTHEW SALESMAN 1600 1500