CODEWITHSUNDEEP
google-bigquerylegacy-sql
jeremieca
jeremieca

Reputation: 1188

How to paginate results in Legacy SQL

We are using Legacy SQL on a specific request. We can't use standard SQL for some internal reasons.

We would like to paginate our results, because we have a lots of rows. Like that :

SELECT ... FROM ... LIMIT 10000 30000 // In standard SQL

But in Legacy SQL Offset don't exists. So how to do the same job ?

Edit :

I don't want to order. I want to paginate. For example get 1000 rows after skipping 2000 rows. A simple LIMIT clause with an offset, like in traditional SQL Database or like in BigQuery Standard SQL. To do this, I want to use Big Query Legacy SQL.

Upvotes: 0

Views: 516

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

The pagination you talking about is done via tabledata.list API

Based on your question and follow-up comments - It might be the way for you to go. Even though it does not involve querying. Just API or related method in client of your choice.
pageToken parameter allows you to page result
Btw, another benefit of this approach - it is free of charge

If you still need to do pagination via query - you option is using ROW_NUMBER() In this case - you can prepare your data in temp table with below query 

SELECT <needed fields>, ROW_NUMBER() OVER() num
FROM `project.dataset.table`

Then, you can page it using num 

SELECT <needed fields>
FROM `project.dataset.temp`
WHERE num BETWEEN 10000 AND 30000

Upvotes: 3

Related Questions