All Forums Database
sunitha_cv 12 posts Joined 05/09
10 Feb 2010
To get a latest record in teradata table

Hi All,I have a table we'll call PhoneTable:ID Phone LastUpdated999999 555-1234 1/1/01999999 555-3456 4/21/02999999 555-7890 9/18/04999998 555-9998 4/20/05999998 555-9998 5/20/06I'd like the output to give me only the most recently inserted record, so, the highest LastUpdated date for each ID:999998 555-9998 5/20/06In Sql, using rowid and rownum function we can achieve the result. But how can we get it in teradata since we can not use those 2 function in teradata 12 version.Pls anyone help me to get the result.Thank you in advance,

dnoeth 4628 posts Joined 11/04
10 Feb 2010

Hi suni,"In Sql, using rowid and rownum function we can achieve the result."You probably mean "in Oracle SQL", because rowid and rownum is no Standard SQL :-)select *from PhoneTablequalify row_number() over (partition by id order by LasUpdated desc) = 1 Dieter

Dieter

SRJ 1 post Joined 02/10
17 Feb 2010

Hi,You can use RANK function or TOP N function in teradata.

mehaboob 10 posts Joined 12/09
18 Feb 2010

You can get the latest record using an identity column. If u have identity column like timestamp then u can get the latest record as max(timestamp) group by PI values. If you don't have any thing like timestamp then append one more column in the table definition as an identity column and you can use that to get the latest record.( I don't think this will effect the performance)

You must sign in to leave a comment.