Teradata - limiting the results using TOP

sql teradata
Aanand Natarajan picture Aanand Natarajan · Sep 5, 2011 · Viewed 12k times · Source

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

you cad sir - take that picture you cad sir - take that · Sep 5, 2011

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.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More