Teradata SQL - LEAD and LAG OLAP Functions

visibility 4,518 event 2020-03-15 access_time 3 years ago language English
more_vert

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.

2020031534050-image.png

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

2020031534940-image.png

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts