Select First Row in Each GROUP BY Group in Teradata

Raymond Raymond visibility 7,215 event 2020-08-26 access_time 4 years ago language English

As other databases, Teradata provides many aggregation functions that can be used to retrieve SUM, COUNT, MAX, MIN and AVG values. These functions returns a single value per group. To retrieve the whole first row from each 'GROUP BY' group, windowing functions can be used. 

Create sample table 

The following code snippet creates a sample table which will be used to query against. 

create set table TestDb.test_table
id int not null,
category varchar(10),
amount int
primary index (id);

insert into TestDb.test_table values(1,'A',10);
insert into TestDb.test_table values(2,'A',11);
insert into TestDb.test_table values(3,'A',12);
insert into TestDb.test_table values(4,'B',100);
insert into TestDb.test_table values(5,'B',101);
insert into TestDb.test_table values(6,'B',102);
insert into TestDb.test_table values(4,'C',1000);
insert into TestDb.test_table values(5,'C',1001);
insert into TestDb.test_table values(6,'C',1002);

The content of the table looks like the following:

id category amount
1 A 10
2 A 11
3 A 12
4 B 100
4 C 1,000
5 B 101
5 C 1,001
6 B 102
6 C 1,002

Use FIRST_VALUE function

FISRT_VALUE returns the first value of an ordered set of values. 


select id, category, amount, 
first_value(amount) over (partition by category order by id) as first_amount
from TestDb.test_table;



Window aggregation function

Windows aggregation function MIN can be used to retrieve the minimum value of a window group. 


select id, category, amount, 
first_value(amount) over (partition by category order by id) as first_amount,
min(amount) over (partition by category) as min_amount
from TestDb.test_table;



Retrieve first row in each group

ROW_NUMBER function returns sequential numbers for each row in the window partition and then QUALIFY can be used to eliminate records. 


select id, category, amount
from TestDb.test_table
qualify row_number() over (partition by category order by id) = 1



The sample SQL statement returns returns the first row for each category value. 

More from Kontext
info Last modified by Administrator 4 years ago copyright This page is subject to Site terms.
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts