Add a sequential number on create / insert - Teradata

Question

In oracle we would use rownum on the select as we created this table. Now in teradata, I can't seem to get it to work. There isn't a column that I can sort on and have unique values (lots of duplication) unless I use 3 columns together.

The old way would be something like,

create table temp1 as 
  select
    rownum as insert_num,
    col1,
    col2,
    col3
  from tables a join b on a.id=b.id
;

Answer 1

This is how you can do it:

create table temp1 as 
( 
   select
      sum(1) over( rows unbounded preceding ) insert_num
     ,col1
     ,col2
     ,col3
   from a join b on a.id=b.id
) with data ;