CODEWITHSUNDEEP
pythonsqlrubygoogle-bigquery
user2989892
user2989892

Reputation: 63

Bigquery query limits upper and lower bounds

On mysql I would enter the following query, but running the same on google BigQuery throws an error for the upper limit. How do I specify limits on a query? Say I have a query that returns 20 results and I want results between 5 and 10 only, how should I frame the query on Google BigQuery?)

For example:

SELECT id,
       COUNT(total) AS total 
FROM ABC.data 
GROUP BY id 
ORDER BY count DESC 
      LIMIT 5,10;

If I only put "LIMIT 5" on the end of the query, I get the top 5 and if I put "LIMIT 10" I ge t the top 10, but what syntax do I use to get between 5 and 10.

Could someone please shed some light on this?

Any help is much appreciated.

Thanks and have a great day.

Upvotes: 1

Views: 2955

Answers (3)

Jordan Tigani
Jordan Tigani

Reputation: 26617

The windowing function answer is a good one, but I thought I'd give another option that involves how your result is fetched rather than how the query is run.

If you only need the first N rows you can add a LIMIT N to your query. But if you don't need the first M rows, you can change how you fetch the results. If you're using the the java API, you can use the setStartIndex() method on either the TableData.list() or the Jobs.getQueryResults() call to only fetch rows starting from a particular index.

Upvotes: 1

N.N.
N.N.

Reputation: 3172

I would use window functions... something like

select * from 
(Select id, total, row_number() over (order by total desc) as rnb
from
(SELECT id,
       COUNT(total) AS total 
FROM ABC.data 
GROUP BY id 
))
where rnb>=5 and rnb<=10

Upvotes: 4

Back2Basics
Back2Basics

Reputation: 7806

That question makes no sense to an ever changing dataset. if you have a 1 second delay between when you ask for the first 5 and the next 5... the data could have changed. It's order is now different and you will miss data or get duplicate results. So databases like BigTable have a method for doing one query of the data and giving you the resultset to you in groups. If that were the case: What you are looking for is called query cursors. I can't say this any better than their own example so [Here is the documentation on them.][1]

But since you said the data does not change then fetch() will work just fine. fetch() has 2 options you will want to take note of limit and offset. 'limit' is the maximum number of results to return. If set to None, all available results will be retrieved. 'offset' is how many results to skip. Check out other options here: https://developers.google.com/appengine/docs/python/datastore/queryclass#Query_fetch

Upvotes: 0

Related Questions