FIRST_VALUE/LAST_VALUE Function Example | Teradata Vantage - 17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

The following example returns by start date the salary, moving average (ma), and first and last salary in the moving average group.

The functions are going to return the first/last value in the window. In the example, the first and last rows fall within the window. If the window were between 3 preceding and 2 preceding rows, you would see NULL for first value in the 1st two rows.
SELECT start_date, salary,
       AVG(salary) OVER(ORDER BY start_date
       ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma,
       FIRST_VALUE(salary) OVER(ORDER BY start_date
       ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) first,
       LAST_VALUE(salary) OVER(ORDER BY start_date
       ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) last
FROM employee
ORDER BY start_date;
start_date salary ma first last
21-MAR-76 6661.78 6603.280 6661.78 6544.78
12-DEC-78 6544.78 5183.780 6661.78 2344.78
24-OCT-82 2344.78 4471.530 6661.78 2344.78
15-JAN-84 2334.78 4441.780 6661.78 4322.78
30-JUL-87 4322.980 4688.980 6544.78 7897.78
31-DEC-90 7897.78 3626.936 2344.78 1234.56
25-JUL-96 1234.56 3404.536 2334.78 1232.78
17-SEP-96 1232.78 3671.975 4322.78 1232.78