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: stackoverflow.com/questions/69473068/… — user13523921
– user13523921, Commented Feb 14, 2023 at 9: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.
– Kate S.D., Commented Feb 14, 2023 at 10:22
If I understand the documentation correctly, the DB engine already computes the offsets. You just have to define the limits and iterate the cursor. — user13523921
– user13523921, Commented Feb 14, 2023 at 12:55

Andrew · Accepted Answer · 2023-02-14 16:58:33Z

1

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

answered Feb 14, 2023 at 16:58

Andrew

8,8933 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Teradata SQL selecting successive batch of rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related