equivalent of rownum in td

Analytics

equivalent of rownum in td

Can you tell me what is equivallent
of rownum in teradata
in oracle I can use
select rownum,ename from emp

What is its equivalent query in teradata?
6 REPLIES
SN
Enthusiast

Re: equivalent of rownum in td

hi jack,

I believe you want to use rownum to limit the number of rows retrieved. as far as i know, there is no system column in teradata to do this, but one can generate a column like a seq_num to retrieve specific interval of rows. if the order is not required, you can use SAMPLE to return a specific number of rows, though randomly.

sel * from this wil return 10 rows selected randomly.

thx,
Junior Contributor

Re: equivalent of rownum in td

Hi Jack,
it's ROW_NUMBER() OVER (ORDER BY ...)

Btw, Oracle also supports that RW_NUMBER.

Dieter

Re: equivalent of rownum in td

select top n columnlist from tablename;

where n is the number of rows you want.

not sure how, but it works.
Enthusiast

Re: equivalent of rownum in td

Is it possible to uniquely identify a row within a group of duplicate records ?
Like the ROWID available in oracle.

Scenario
----------
id name
--- -------
1 xxxx
2 xxxxx
1 xxxx
1 YYYY

I want to delete or update only the first record in the above scenario , Please do advise.

Junior Supporter

Re: equivalent of rownum in td

>>"Is it possible to uniquely identify a row within a group of duplicate records ? Like the ROWID available in oracle."

No. The Oracle rowid relates to the store (datafile...). And it is NOT immuttable btw...

>>"I want to delete or update only the first record in the above scenario , Please do advise."

There are several ways to accomplish. You can find one of them here (in spanish, as always):

http://carlosal.wordpress.com/2009/12/07/borrando-filas-duplicadas-en-teradata/

HTH.

Cheers.

Carlos.
Enthusiast

Re: equivalent of rownum in td

Hi Carlos,

Thanks for your comments, yes you are correct did not think about the qualify and row_number function