Teradata - limiting results with TOP

Question

I am trying to extract a huge set of records from Teradata using JDBC. And I need to split this set into parts for which I use the "Top N" clause in select. But I don’t know how to set the "Offset" in the same way as we do in MySQL -

SELECT * FROM tbl LIMIT 5,10 

so the next select statement will select me records from the (N + 1) position.

Answer 1

RANK and QUALIFY I believe your friends are here

eg

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

The RANK (field) will (conceptually) retrieve all the rows of the result set, order them in the ORDER BY field and assign them an incremental rank identifier.

QUALIFY allows you to trim this by restricting the strings returned to the qualification expression that RANK can now legitimately scan.

To be clear, I return the 900-1000th lines in the query, select all from cusotmers, NOT returning clients with identifiers from 900 to 1000.

Answer 2

You can also use the window aggregate ROW_NUMBER in Teradata.

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

Unlike the RANK window aggregate, ROW_NUMBER will provide you with a sequence, regardless of whether the column that you order from an additional set of sections is unique or not.

Another option to consider.