ROW number based on order of Insertion

Database

ROW number based on order of Insertion

Hi All,

Normally, we can find row_number based on the order of columns using ROW_NUMBER() function.

select row_number() over(order by col1) from tablename ;

Is there any way in Teradata to get the Row number based on the order/time of insertion rather than the order of columns contained.

Thanks in advance.

4 REPLIES
Teradata Employee

Re: ROW number based on order of Insertion

Hi,

you could save timestamp of insertion into a separate column (during inserts), and use it for that purpose.  It would take more perm space to store the table though.

Regards,

Vlad.

Re: ROW number based on order of Insertion

Hi Vlad,

Thanks for your response. Will try that. Meanwhile, Is there any option to acheive the same thing within the scope of Row_number() function. I wanted to convert an existing sybase code into Teradata.

In sybase  'select rowid(a) from table1 as a' displays row number based on the order of insert. Will it work similarly in Teradata too? I tried but in vain. Is there any other option  that can be used along with Row_number() function to get the row number based on the order of insert.

Regards,

Bala.




Teradata Employee

Re: ROW number based on order of Insertion

Bala,

Not that I'm aware of, sorry.  Rowids in Teradata do not impose sequential order the way you describe it.    

Thank you,

Regards,

Vlad.

Re: ROW number based on order of Insertion

Bala,

I dont think you will be able to select the rows the same way you had inserted. Though one way would be, if you are inserting into a table in a particular order try to insert the row number for that particular order as one column and while selecting use order by on that extra column.

While inserting if you dont specify any order(and put that row_number as one column), i dont think its possible at all to select in any particular order.

Also inserting timestamp wont work, because for most of the records you will have same timestamp.