Teradata SQL selecting successive batch of rows

Question

I have 300000 entries in my db and am trying to access entry 50000-100000 (to 50000 total).

My query is as follows:

query = 'SELECT TOP 50000* FROM database ORDER BY col_name QUALIFY ROW_NUMBER() BETWEEN 50000 and 100000'

I only found the BETWEEN KEYWORD in one source however and am suspecting I am not using it correctly since it says it can't be used on a non-ordered database. I assume the QUALIFY then gets evaluated before the ORDER BY.

So I tried something along the lines of

query_second_try = 'SELECT TOP 50000* FROM database QUALIFY ROW_NUMBER() OVER (ORDER BY col_name)'

to see if this fixes the problem (without taking into account the specific rows I want to select). This is also not the case.

I have tried using qualify with rank, but this doesn't seem to be exactly what I need either, I think the BETWEEN statement would be a better fit.

Can someone push me in the right direction here? I am essentially trying to do the equivalent of 'ORDER BY col_name OFFSET BY 50000' in teradata.

Any help would be appreciated.

Maybe this post answers your question: https://stackoverflow.com/questions/69473068/teradata-equivalence-for-limit-offset-sql-paged — , Feb 14 '23 at 09:53
Hi, I had actually come across that post but the code doesn't function. Should have mentioned it in my post! — Kate S.D., Feb 14 '23 at 10:22
If I understand the [documentation](https://www.docs.teradata.com/r/Teradata-Aster-Client-Guide/May-2017/Aster-Database-Cluster-Terminal-ACT/Using-ACT/Throttle-Query-Results-in-ACT-and-Aster-Database/The-SQL-LIMIT-Clause-vs.-Fetch-Limit-and-Fetch-Count) correctly, the DB engine already computes the offsets. You just have to define the limits and iterate the cursor. — , Feb 14 '23 at 12:55

score 1 · Accepted Answer · answered Feb 14 '23 at 16:58

Few problems here.

row_number requires an order by. And it needs to be granular enough to ensure it's deterministic. You can also play around with rank, dense_rank, and row_number, depending on what you want to do with ties.

You're also mixing top N and qualify.

Try this:

select
*
from
<table>
qualify row_number() over (order by <column(s)>) between X and Y

Teradata SQL selecting successive batch of rows

1 Answers1