access_time 9 months ago languageEnglish
more_vert

Select First Row in Each GROUP BY Group in Teradata

visibility 1,589 comment 0

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. 

Query:

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

Result:

Window aggregation function

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

Query:

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;

Result:

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. 

Query:

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

Result:

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

info Last modified by Administrator 9 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts