Teradata SQL - LEAD and LAG OLAP Functions

visibility 3,274 access_time 3 years ago languageEnglish

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


copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 4.11
More from Kontext
Teradata ISNULL Alternatives
visibility 811
thumb_up 0
access_time 2 years ago
Create Multiset Temporary Table in Teradata
visibility 2,047
thumb_up 0
access_time 2 years ago
Save Spark DataFrame to Teradata and Resolve Common Errors
visibility 651
thumb_up 0
access_time 2 years ago