Are big-query results always ordered, that is: using OFFSET makes sense to skip rows?

0
votes

In other words does a select query order results every time, so these 2 will always produce unique values:

select * from bigquery-public-data.crypto_ethereum.balances limit 10 OFFSET 100

select * from bigquery-public-data.crypto_ethereum.balances limit 10 OFFSET 2000

Assuming of course the table has unique values...I am just curious if without using "order" clause the table is always deterministic/consequetive or can the results duplicate if they're returned indeed at random? 10x!

1
sqlselectgoogle-bigquerysql-order-by
Documentation is very clear on this - The rows that are returned by LIMIT and OFFSET are unspecified unless these operators are used after ORDER BY - see more in LIMIT clause and OFFSET clause - Mikhail Berlyant

1 Answers

0
votes

I am just curious if without using "order" clause the table is always deterministic/consequetive or can the results duplicate if they're returned indeed at random.

No. SQL tables represent unordered set of rows. There is no inherent ordering of the rows. Unless an order by clause is specified, there is no guarantee that two consequent executive of the same query would yield an indentical result. The database is free to return the rows in whatever order it likes.

As a consequence, the results of a query with a row-limiting clause but no order by clause are not deterministic. Do add an order by clause the these queries, or you will sooner or later run into suprising and hard-to-debug behaviors.