Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a table with 7 columns and ~8.5 mil rows. I'm attempting to select into a destination table with "Allow large results" checked. Note I've already had to decompose a larger query into multiple steps because of resource exceeded errors.

SELECT col1, col2, col3, col4, RANK() OVER (PARTITION BY col1 ORDER BY col4 DESC) rank FROM [dataset.table]

This returns a "resources exceeded" error.

share|improve this question
add comment

1 Answer

Due to the current implementation of the window functions, these errors are kind of expected when trying to run window functions over big datasets (as in this case, that requires the large results flag).

While these limitations are in place, I would suggest running the query in multiple steps, as in:

SELECT col1, col2, col3, col4, RANK() OVER (PARTITION BY col1 ORDER BY col4 DESC) rank FROM [dataset.table]
WHERE ABS(HASH(col1)) % 4 = 0

(replace 0 with 1, 2, and 3 to complete the whole process - or 4 with a bigger number if resources still exceeded)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["        <li><a href=\"http://area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"http://careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2013 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc-wiki</a> \r\n with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"footer-flair\">\r\n                <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" class=\"cc-wiki-link\"></a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2013.12.6.1206\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflo", "ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2013 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc-wiki</a> \r\n with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"footer-flair\">\r\n                <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" class=\"cc-wiki-link\"></a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2013.12.6.1206\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </noscript>\r\n<script type=\"text/javascript\">var p = \"http\", d = \"static\"; if (document.location.protocol == \"https:\") { p += \"s\"; d = \"engine\"; } var"], "copyright__copyright__copyright": ["eps, as in:\n\nSELECT col1, col2, col3, col4, RANK() OVER (PARTITION BY col1 ORDER BY col4 DESC) rank FROM [dataset.table]\nWHERE ABS(HASH(col1)) % 4 = 0\n\n(replace 0 with 1, 2, and 3 to complete the whole process - or 4 with a bigger number if resources still exceeded)\n\nshare|improve this answer\nadd comment\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}