TeraData equivalence for limit offset SQL - PAGED

Question

In MySQL and other engines I use a statement of the type:

SELECT reference FROM table WHERE field = 'iphone' ORDER BY reference LIMIT 2  OFFSET  0;

But in TeraData I can't find an equivalency to perform a paginated query.

I appreciate your ideas ;)

Does this answer your question? pagination in teradata in ascending order — Shadow
– Shadow, Commented Oct 6, 2021 at 22:08

David AJ · Accepted Answer · 2021-10-06 22:40:48Z

1

I found this options

1.

SELECT RANK(reference) as row_num, reference 
  FROM table 
 WHERE field = 'iphone'
ORDER BY 1
QUALIFY row_num  BETWEEN 2 and 4;

SELECT  RANK() OVER (ORDER BY reference) as row_num, reference 
  FROM table 
 WHERE field = 'iphone'
QUALIFY row_num  BETWEEN 2 and 4;

But I'm not sure which one is the best

edited Oct 6, 2021 at 22:40

answered Oct 6, 2021 at 22:30

David AJ

391 silver badge5 bronze badges

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

2 Comments

Andrew Over a year ago

Run the select without the qualify, you should be able to see the difference pretty quickly.

Fred Over a year ago

#1 is legacy TD-specific syntax; #2 is preferred going forward. Note that if there are duplicate ORDER BY values you may not get exactly the number of rows you expect (but at least you should not skip or repeat rows since duplicates would all be assigned the same RANK)

Collectives™ on Stack Overflow

TeraData equivalence for limit offset SQL - PAGED

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related