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.