The relational databases like Oracle supports the ROWNUM pseudo column. If you are from Oracle background, you may have used ROWNUM extensively to limit the output result set. But, There is no ROWNUM in Teradata. In this article, we will check Teradata ROWNUM pseudocolumn alternative with some examples.
Teradata ROWNUM Pseudocolumn Alternative
A ROWNUM is a pseudo column, which indicates the row number in a result set retrieved by a SQL query. It starts by assigning 1 to the first row and increments the ROWNUM value with each subsequent row returned. In the databases like Oracle, you can use ROWNUM to limit the result set. For example, retrieve only top 10 records from SQL query. In other words, ROWNUM is very useful internal row.
There are a couple of methods that you can use as an ROWNUM pseudo column alternatives in Teradata. Such as,
- User TOP Keyword to Limit Records
- Use ROW_NUMBER analytic Function
Now, let us check these methods in brief.
Teradata TOP Keyword as an Alternative to ROWNUM
The ROWNUM is primarily used to return limited record sets from the query result. The same functionality can be achieved by using Teradata TOP keyword.
For example, consider below example to return only top 10 records from the result set.
SELECT TOP 10 ss_sold_date_sk,ss_item_sk
FROM STORE_SALES;
ss_sold_date_sk ss_item_sk
1 2,451,192 1,855
2 2,451,192 3,914
3 2,451,192 4,315
4 2,451,192 4,408
5 2,451,192 6,112
6 2,451,192 7,004
7 2,451,192 4,724
8 2,451,192 2,680
9 2,451,192 316
10 2,451,192 44
The TOP keyword is similar to the LIMIT function any most of MPP databases.
ROW_NUMBER analytic Function As an ROWNUM Equivalent
The TOP command is the easy one to use. Similarly, you can use the ROWNUM analytic function as an ROWNUM equivalent.
For example, consider below query.
SELECT ss_sold_date_sk,
ss_item_sk
FROM (SELECT Row_number()
OVER(
ORDER BY ss_sold_date_sk, ss_item_sk) AS ROWNUM,
ss_sold_date_sk,
ss_item_sk
FROM store_sales) tmp
WHERE rownum <= 10;
ss_sold_date_sk ss_item_sk
1 2,451,192 1,855
2 2,451,192 3,914
3 2,451,192 4,315
4 2,451,192 4,408
5 2,451,192 6,112
6 2,451,192 7,004
7 2,451,192 4,724
8 2,451,192 2,680
9 2,451,192 316
10 2,451,192 44As you can see in the example, the ROW_NUMBER function support ORDER BY and PARTITION keywords. To generate row numbers, better use only ORDER BY clause.
Meanwhile, the analytic functions are expensive when you used with ORDER BY clause.
Related Articles
Hope this helps 🙂