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.

Answer 1

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.

Answer 2

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.