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](https://stackoverflow.com/questions/34319959/pagination-in-teradata-in-ascending-order) — Shadow, Oct 06 '21 at 22:08

David AJ · Answer 1 · 2021-10-06T22:40:48.410

0

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 06 '21 at 22:40

answered Oct 06 '21 at 22:30

David AJ

Run the select without the qualify, you should be able to see the difference pretty quickly. – Andrew Oct 07 '21 at 01:43
2

#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) – Fred Oct 07 '21 at 14:07

1 Answers1