Teradata First and Last Value

Question

How to take first value and last value of a column with group by on a particuar column? For eg:i need first_value and last_value of case_owner column based on group by og case id.

see this:http://stackoverflow.com/questions/25170215/get-values-from-first-and-last-row-per-group — Suchit kumar, Nov 18 '15 at 07:02
You can use the windows agg function like row_number() or rank() . — anwaar_hell, Nov 18 '15 at 09:08

score 2 · Answer 1 · answered Nov 18 '15 at 09:14

For first value:

select case_owner  as case_owner_first_value
from
table
qualify row_number() over (partition by case_id  order by case_id) = 1

For last value:

select case_owner  as case_owner_last_value
from
table
qualify row_number() over (partition by case_id order by case_id desc ) = 1

score 2 · Answer 2 · answered Nov 06 '19 at 14:36

Please note, while combining the FIRST_VALUE with ORDER BY clause, you may need to add rows between.

Example:

CREATE VOLATILE TABLE test_fv (id INTEGER,seq SMALLINT) ON COMMIT PRESERVE ROWS;

INSERT INTO test_fv VALUES (NULL,1);
INSERT INTO test_fv VALUES (2,2);
INSERT INTO test_fv VALUES (3,3);
INSERT INTO test_fv VALUES (4,4);
INSERT INTO test_fv VALUES (5,5);

SELECT seq, FIRST_VALUE(id ignore NULLS) OVER (ORDER BY seq ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM test_fv;

This wont work:

SELECT seq, FIRST_VALUE(id ignore NULLS) OVER (ORDER BY seq ASC) FROM test_fv;

Teradata First and Last Value

2 Answers2