Search code examples
sqlfilterprestorow-numbertrino

Presto: Filtering by Row Number

Can anyone help me to translate Teradata SQL QUALIFY ROW_NUMBER() OVER into Presto:

SELECT * 
FROM table1
QUALIFY ROW_NUMBER() OVER(ORDER BY id DESC) > 5000000 
AND ROW_NUMBER() OVER(ORDER BY id DESC) <= 10000000;

Or provide some suggestions how to extract large datasets by row filtering.

Solution

  • As far as I understand there is no direct analog for QUALIFY clause in PrestoSQL/Trino. You can just use ROW_NUMBER window function in subquery (or CTE) and then filter based on that. Something like the following:

    SELECT col_list_except_rn
FROM (SELECT *,
        ROW_NUMBER() OVER(ORDER BY id DESC) rn
    FROM table1)
WHERE rn BETWEEN 5000001 AND 10000000;