Select First Row in Each GROUP BY Group in Teradata
insights Stats
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.