All Forums Database
dbnovice 2 posts Joined 10/08
09 Oct 2008
elementary question

I have to pick a set of records in a table with a certain hierarchy. for example:email address, taxid,location, sectionthe hierarchy is based on the sectionsection BX is the highest so any record with BB should be picked over otherssection SX is next section TX 3rd etc...if i have the following records in my table email taxid location section123@aol.com 123457 denver BB123@aol.com 123457washington SX123@aol.com 123457 florida TXhow can I pick the BB record?and if I have other records in the same file that have567@aol.com,56789,florida,SX567@aol.com,56789,flor ida,TX how can I select only the highest hierarchy of SX?thanks for your help!

dbnovice 2 posts Joined 10/08
09 Oct 2008

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 = 1but I didn't think row_number worked in Teradata.thanks in advance.

dnoeth 4628 posts Joined 11/04
09 Oct 2008

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) = 1Dieter

Dieter

Fred 1096 posts Joined 08/04
09 Oct 2008

This is users helping users, so it depends on how busy we all are...ROW_NUMBER has been available in Teradata for years.

You must sign in to leave a comment.