Teradata - limiting the results using TOP

Question

I am trying to fetch a huge set of records from Teradata using JDBC. And I need to break this set into parts for which I'm using "Top N" clause in select. But I dont know how to set the "Offset" like how we do in MySQL -

   SELECT * FROM tbl LIMIT 5,10

so that next select statement would fetch me the records from (N+1)th position.

score 7 · Accepted Answer · answered Sep 05 '11 at 13:09

7

RANK and QUALIFY I beleive are your friends here

for example

 SEL RANK(custID), custID 
 FROM mydatabase.tblcustomer
 QUALIFY RANK(custID) < 1000 AND RANK(custID) > 900
 ORDER BY custID;

RANK(field) will (conceptually) retrieve all the rows of the resultset, order them by the ORDER BY field and assign an incrementing rank ID to them.

QUALIFY allows you to slice that by limiting the rows returned to the qualification expression, which now can legally view the RANKs.

To be clear, I am returning the 900-1000th rows in the query select all from cusotmers, NOT returning customers with IDs between 900 and 1000.

answered Sep 05 '11 at 13:09

lifeisstillgood

3,265
2
21
22

I would suggest using the window aggregate version of `RANK({column})` going forward. RANK as shown in this answer has been deprecated since Teradata 12. `RANK() OVER (ORDER BY custID) AS Rank_` and then `QUALIFY on Rank_ BETWEEN 900 AND 1000`. If you include the `PARTITION BY` (optional) clause you can reset the ranking when the partitioning set of columns changes. Keep in mind that if the column you are ordering by is not unique RANK will not provide you a pure sequence. – Rob Paller Sep 06 '11 at 13:17
`RANK()` corresponds to `TOP n WITH TIES` semantics. For `TOP n` or `LIMIT` semantics, `ROW_NUMBER()` should be used. – Lukas Eder Apr 19 '18 at 11:01

score 6 · Answer 2 · answered Sep 06 '11 at 13:24

You can also use the ROW_NUMBER window aggregate on Teradata.

SELECT ROW_NUMBER() OVER (ORDER BY custID) AS RowNum_
     , custID
  FROM myDatabase.myCustomers
QUALIFY RowNum_ BETWEEN 900 and 1000;

Unlike the RANK windows aggregate, ROW_NUMBER will provide you a sequence regardless of whether the column you are ordering over the optional partition set is unique or not.

Just another option to consider.

Teradata - limiting the results using TOP

2 Answers2

Linked