BigQuery INSERT SELECT results in random order of records?

Question

I used standard SQL to insert data form one table to another in BigQuery using Jupyter Notebook.

For example I have two tables:

table1

    ID  Product
0   1   book1
1   2   book2
2   3   book3

table2

    ID  Product Price
0   5   book5   8.0
1   6   book6   9.0
2   4   book4   3.0

I used the following codes

INSERT test_data.table1
SELECT *
FROM test_data.table2
ORDER BY Price;

SELECT *
FROM test_data.table1

I got

    ID  Product
0   1   book1
1   3   book3
2   2   book2
3   5   book5
4   6   book6
5   4   book4

I expected it appears in the order of ID 1 2 3 4 5 6 which 4,5,6 are ordered by Price

It also seems that the data INSERT and/or SELECT FROM display records in a random order in different run.

How do I control the SELECT FROM output without including the 'Price' column in the output table in order to sort them?

And this happened when I import a csv file to create a new table, the record order is random when using SELECT FROM to display them.

You need a definitive column in order to sort results from BQ. — khan

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-02-02T02:25:11

The ORDER BY clause specifies a column or expression as the sort criterion for the result set.
If an ORDER BY clause is not present, the order of the results of a query is not defined.
Column aliases from a FROM clause or SELECT list are allowed. If a query contains aliases in the SELECT clause, those aliases override names in the corresponding FROM clause.

So, you most likely wanted something like below

SELECT *
FROM test_data.table1
ORDER BY Price DESC 
LIMIT 100

Note the use of LIMIT - it is important part - If you are sorting a very large number of values, use a LIMIT clause to avoid resource exceeded type of error

BigQuery INSERT SELECT results in random order of records?

1 Answers