rownum in teradata

Database
Enthusiast

rownum in teradata

hi,

i want to get rid of duplicate rows in teradata table..

for example if i have a table with the following fields

customerid customename customeraddress
1 mac okc
2 mac NJ

In this customerid and customername are the primary index..So the above two records are the duplicate records but fact data fileds(customeraddress) differs

Now ,
I need to select the data from the above duplicate rows which is the latest one i.e (2,mac,Nj)

i can use rowid in oracle to retrieve this..
like

select * from t1
where rowid not in
(select max(rowid)
from t1
group by customerid,customername)

i dont think there is a pseudo column row id in teradata...

can any one help me in retrieving latest record from table where there are duplicate records with same column primary index fields
thank you

5 REPLIES
Enthusiast

Re: rownum in teradata

You are right. There is no pseudo column "rowid" in teradata.

If you want to eliminate duplicate records (basically all columns having same value in more than one record), then you can create a SET table. Do simple insert into select * from. It will silently eliminate duplicates.
Note that SET table can not allow duplicate records, MULTISET table allows duplicate records.

But in case you specified, you want to get latest record based on set of columns if they repeat. You can do this using ROW_NUMBER() function. I am assuming you have a modified_ts in your table to identify latest record. If so, below query can be used to get latest records.

select
customerid,
customename,
customeraddress,
ROW_NUMBER() OVER(PARTITION BY customerid, customername ORDER BY modified_ts DESC) rn
from
customer
QUALIFY rn=1

Hope this answers your question.
Enthusiast

Re: rownum in teradata

hi ravi...
thanks for u r quick response..
I don't have column like modified_ts to get the latest record...

Before, there is an identity column in the table by which we can get the latest record..
But i want to get rid of the identity column..

let me give u a brief overview about my issue

if i have duplicate records with same primary index but with different fact column values then
i need to update the latest record's flag as 'A' ignoring all the previous duplicate records

as i said before

if i have data like
cusid name location flg
1 mac OKC A
1 mac TN A
1 mac NJ A

mac with cusid 1 has three records in today's data..
Among them i need to update the flg as 'Dup' for all the records except latest one

so the reuslt should look like this

cusid name location flg
1 mac OKC Dup
1 mac TN Dup
1 mac NJ A

i dont have any identity column to identify the latest record...

i hope u got the clear picture of my problem..

thanks once again

Enthusiast

Re: rownum in teradata

I understand your problem. But i am unable to solve the problem without having a column to identify a latest record. Only if i know the latest record then i can flag other records with "Dup".

I don't think we will be able to flag a record without a way to find which is latest record.
Senior Apprentice

Re: rownum in teradata

How is "latest record" defined on your system?
Based on the time of insert, but without a TIMESTAMP column?
This will probably fail in most DBMSes.

Dieter
Fan

Re: rownum in teradata

Hi Guys,

How will be the data in column modified_ts ? Can any one give Idea on that.

Regards
Rakesh