6

I need to pull a row ID with a select statement. Something that is similar to the row ID of oracle. How would I do that in Teradata? I am trying the following query but it is throwing error.

select rowid,emp_id,e_name from test;

Error msg : Syntax error: ROWID not allowed.

Thanks in advance.

Rob Paller
  • 7,736
  • 29
  • 26
LazyCoder
  • 336
  • 2
  • 8
  • I found something that might solve your problem http://forums.teradata.com/forum/database/rowid-in-teradata but I am not sure if it will work in your case – AurA Jan 23 '13 at 10:33
  • Hi AurA, I tried the same query which is mentioned in the link provided by you. But it is throwing an error like 'ROWID not allowed'. – LazyCoder Jan 24 '13 at 06:34

1 Answers1

4

Try the ROW_NUMBER() window aggregate function:

SELECT ROW_NUMBER() OVER(ORDER BY empid) AS RowID_
     , empid
     , empName
  FROM test;

Unlike RANK() the ROW_NUMBER() window aggregate will not permit ties to occur in the result set. Your other alternative would be to use an IDENTITY column in your table but those are messy and cause problems down the road. It is easier to incorporate ROW_NUMBER() in your ETL processing if you need to maintain a surrogate key.

It should be noted that if your ORDER BY or PARTITION BY column is skewed, your performance on the STAT FUNCTION step in the query plan me be impacted for large sets of data. The PARTITION BY clause is optional and allows you to define a window where the result of the ROW_NUMBER() would be reset when the partition changes. Not commonly used with ROW_NUMBER() but it may come in handy.

Edit To uniquely identify the record with a surrogate and not have to rely on logic in your ETL use and identity column in your table. Configured correctly the IDENTITY column will not reuse any domain values when records are deleted.

Rob Paller
  • 7,736
  • 29
  • 26
  • 4
    Also note that this is not the same thing as an Oracle "rowid". In Oracle, a "rowid" is the permanent address of a particular row in a table (at least that's how I think it works). Using `ROW_NUMBER` will give different results if new rows have been inserted into the table. I don't think there is a similar concept in Teradata. Just a caution and +1. – BellevueBob Jan 23 '13 at 18:07
  • I need unique id to identify each row and this id should not get change even if we delete some records. – LazyCoder Jan 24 '13 at 06:22
  • See latest edit on using an IDENTITY column in your table for a surrogate. – Rob Paller Jan 24 '13 at 10:54