First let’s recall what ROWNUM in Oracle is. There is a common misconception that the value assigned in ROWNUM is a permanent one, this is incorrect. As you know ROWNUM is a pseudo column and not a real one which is available only during a query processing. The value that you get as ROWNUM is assigned to a row after it passes the parsing phase of the query but before sorting or aggregation.

In Teradata, there is a function called ROW_NUMBER () but this is not a replica of the ROWNUM in Oracle nor it is having any similarity. People often get confused with the similar name and expect the same functionality. ROW_NUMBER() is an aggregate function and works with the “partition’ clause only. So there is no need to get confused with the ROW_NUMBER function as it is NOT the same as ROWNUM in Oracle. But the same (pretty much) functionality as ROWNUM can be implemented in Teradata with the help of TOP.

Following two queries would return the same rows in the same order in Oracle and Teradata respectively.

In Oracle

SELECT *

FROM salary

WHERE ROWNUM <= 2

In Teradata

SELECT TOP 2 *

FROM salary

Popularity: 14% [?]

Our Random Articles

• How To Choose A Good Online Traffic School
• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress

More Links