To get a latest record in teradata table

Database
Enthusiast

To get a latest record in teradata table

Hi All,

I have a table we'll call PhoneTable:

ID Phone LastUpdated

999999 555-1234 1/1/01
999999 555-3456 4/21/02
999999 555-7890 9/18/04
999998 555-9998 4/20/05
999998 555-9998 5/20/06
I'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/06

In 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,
3 REPLIES
Senior Apprentice

Re: To get a latest record in teradata table

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 PhoneTable
qualify
row_number() over (partition by id order by LasUpdated desc) = 1

Dieter
SRJ
N/A

Re: To get a latest record in teradata table

Hi,

You can use RANK function or TOP N function in teradata.
Enthusiast

Re: To get a latest record in teradata table

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)