There is no OFFSET support in AWS Athena, but we can use a workaround to get the same behavior.

Let’s assume that I want to execute this query:

SELECT * FROM some_table
ORDER BY column_A
OFFSET 20 LIMIT 10 -- this doesn't work

to get the desired outcome, I need three things:

  • a window function that assigns a number to every row
  • ordering method that sorts the table, so I get a deterministic outcome
  • filtering function that selects only the rows I want
SELECT * FROM (
    SELECT row_number() over(ORDER BY column_A) AS rn, * FROM some_table)
WHERE rn BETWEEN 20 AND 30; -- 30 = 20 (offset) + 10 (limit)
Stop AI Hallucinations Before They Cost You.
Join engineering leaders getting weekly tactics to prevent failure in customer-facing AI systems. Straight from real production deployments.
Stop AI Hallucinations Before They Cost You.
Join engineering leaders getting weekly tactics to prevent failure in customer-facing AI systems. Straight from real production deployments.
Older post

How to get an alert if an AWS lambda does not get invoked during the last 24 hours

How to get a notification when AWS Lambda stops begin used

Newer post

What is s3:TestEvent, and why does it break my event processing?

S3 sends s3:TestEvent to SQS after setting up the bucket notifications

Engineering leaders: Is your AI failing in production? Take the 10-minute assessment
>
×
Stop AI Hallucinations Before They Cost You.
Join engineering leaders getting weekly tactics to prevent failure in customer-facing AI systems. Straight from real production deployments.