Teradata SQL - LEAD and LAG OLAP Functions

From Teradata 16, LEAD and LAG OLAP functions are supported. The LAG function returns data from a row preceding the current row at a specified offset in a window group; the LEAD function accesses data from a row following the current row at a specified offset in a window group.

Scenario

Table TD_MS_SAMPLE_DB.Employee has the following data. Find the EmployeeID for each employee that has closest salary to them.

Code snippet

select	EmployeeID,EmployeeName,Salary,
LAG(EmployeeID) ignore nulls over (
partition by 1
order by Salary) as EmployeeID_Lag,
LAG(Salary) ignore nulls over (
partition by 1
order by Salary) as Salary_Lag,
LEAD(EmployeeID) ignore nulls over (
partition by 1
order by Salary) as EmployeeID_Lead,
LEAD(Salary) ignore nulls over (
partition by 1
order by Salary) as Salary_Lead
from	TD_MS_SAMPLE_DB.Employee;

Sample output