Teradata SQL - LEAD and LAG OLAP Functions

visibility 3,325 access_time 3 years ago languageEnglish timeline Stats
timeline Stats
Page index 4.13

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

More from Kontext
Create Bucketed Sorted Table - Hive SQL
visibility 658
thumb_up 0
access_time 2 years ago
Create Partitioned Table - Hive SQL
visibility 3,066
thumb_up 0
access_time 2 years ago