Use the lead()
function to get the next row value in Oracle. Below are its syntax and example:
Lead Function Syntax
lead(expr, offset, default_value) over (partition_by_clause order_by_clause)
For the expr
, specify the column name, for offset
, specify the row number, and for the default_value
, specify the value to show the default value for the last row. Also, specify the partition or order by clause for the window function.
Oracle SQL Query Example to Get Next Row Value
The following SQL query will get the data from the EMP table and will display the next row salary in the current row:
select ename, job, sal,
lead(sal, 1, 0) over (order by ename) next_salary
from emp;
Output:
ENAME | JOB | SAL | NEXT_SALARY |
---|---|---|---|
ADAMS | CLERK | 1100 | 1600 |
ALLEN | SALESMAN | 1600 | 2850 |
BLAKE | MANAGER | 2850 | 2450 |
CLARK | MANAGER | 2450 | 3000 |
FORD | ANALYST | 3000 | 950 |
JAMES | CLERK | 950 | 2975 |
JONES | MANAGER | 2975 | 5000 |
KING | PRESIDENT | 5000 | 1250 |
MARTIN | SALESMAN | 1250 | 1300 |
MILLER | CLERK | 1300 | 3000 |
SCOTT | ANALYST | 3000 | 800 |
SMITH | CLERK | 800 | 1500 |
TURNER | SALESMAN | 1500 | 1250 |
WARD | SALESMAN | 1250 | 0 |