Select First Row in Each GROUP BY Group in Teradata

access_time 4 months ago visibility379 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 at 4 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

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer Azure local_offer teradata local_offer teradata-tool

visibility 1017
thumb_up 0
access_time 4 years ago

This page provides the steps to create a Teradata database in Microsoft cloud computing platform Azure.

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 394
thumb_up 0
access_time 4 months ago

This page shows how to create, drop, and truncate Hive tables via Hive SQL (HQL). Refer to  Differences between Hive External and Internal (Managed) Tables to understand the differences between managed and unmanaged tables in Hive.  Example: CREATE TABLE IF NOT EXISTS ...

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 25
thumb_up 0
access_time 4 months ago

This page shows how to create bucketed sorted Hive tables via Hive SQL (HQL). CLUSTERD BY is used to create bucketed table Example: CREATE TABLE IF NOT EXISTS hql.transactions_bucketed(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2),txn_type STRING, created_date DATE) COMMENT 'A table to ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS