elementary question

Database

elementary question

I have to pick a set of records in a table with a certain hierarchy. for example:

email address, taxid,location, section

the hierarchy is based on the section

section BX is the highest so any record with BB should be picked over others
section SX is next
section TX 3rd etc...

if i have the following records in my table

email taxid location section
123@aol.com 123457 denver BB
123@aol.com 123457washington SX
123@aol.com 123457 florida TX

how can I pick the BB record?

and if I have other records in the same file that have

567@aol.com,56789,florida,SX
567@aol.com,56789,florida,TX

how can I select only the highest hierarchy of SX?

thanks for your help!
3 REPLIES

Re: elementary question

Hello,
what's the normal turnaround for this forum?

It's my first time here.

I want to also add that in Oracle I have used
select * from
(
select ,
row_number() over ( partition by email_address order by section) as seq
)
where seq = 1

but I didn't think row_number worked in Teradata.

thanks in advance.
Junior Contributor

Re: elementary question

Why didn't you just try your Oracle query?
ROW_NUMBER is Standard SQL and available in Teradata since several years.
There's even an extension for filtering the result of the OLAP-function, similar to HAVING for GROUP BY:
select ...
QUALIFY row_number() over ( partition by email_address order by section) = 1

Dieter
Teradata Employee

Re: elementary question

This is users helping users, so it depends on how busy we all are...

ROW_NUMBER has been available in Teradata for years.