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