Google BigQuery large table (105M records) with 'Order Each by' clause produce "Resources Exceeds Query Execution" error -

i running serious issue "resources exceeds query execution" when google big query large table (105m records) 'order each by' clause.

here sample query (which using public data set: wikipedia):

select id,title,count(*) [publicdata:samples.wikipedia] group each id, title order id, title desc 

how solve without adding limit keyword.

using order on big data databases not ordinary operation , @ point exceeds attributes of big data resources. should consider sharding query or run order in exported data.

as explained today in your other question, adding allowlargeresults allow return large response, can't specify top-level order by, top or limit clause. doing negates benefit of using allowlargeresults, because query output can no longer computed in parallel.

one option here may try sharding query.

where abs(hash(id) % 4) = 0 

you can play above parameters lot achieve smaller resultsets , combining.

also read chapter 9 - understanding query execution explaines how internally sharding works.

you should read launch checklist bigquery