1

I am running a below query in big query

insert into `test-project.temp_test_dataset.cluster_records`
(pm,col1,col2,col3,total)
select pm,col1,col2,col3,total from (
select 'ingest' as pm,col1,col2,col3,col4,col5,sum(col3+col4) as total from table1
unionall
select 'aggregated' as pm,col1,col2,col3,col4,col5,sum(col3+col4) as total from table2 order by col4
);

The select statement fetch record as:

ingest,AA,BB,CC,10
aggregated,AA,BB,CC,10
ingest,KK,LL,MM,50
aggregated,KK,LL,MM,50

But when it inserts into this table it will insert like below:

ingest,AA,BB,CC,10
ingest,KK,LL,MM,50
aggregated,KK,LL,MM,50
aggregated,AA,BB,CC,10

How to keep the same order as selected records? Basically I would like to get the difference of column total later when rows get inserted to table cluster_records

Is there any way that I can create a some row number for each table records and can use that column with order by clause I am running this query in shell may be something can be done using shell to generate some unique increasing number

Improve this question
5
  • 3
    Relations, as a special form of (multi) sets, don't have an inherit order. I don't know if BigQuery is an exception here, but usually relational tables, which represent such relations, therefore also don't have such an order. Unless there's an explicit ORDER BY in a query, the result can be returned by the DBMS in any order and can even change each time the query is fired. So there might be no order to "keep" here. If you want the record of that target table in a specific order when SELECTing from it, you have to use an explicit ORDER BY each time.
    – sticky bit
    Dec 31, 2020 at 10:40
  • is it possible to add some extra column to keep the order intact? since there is a possibility that existing column have same set of data for different rows
    – vikrant rana
    Dec 31, 2020 at 11:53
  • 1
    Is there a column or a combination of columns in the SELECT you insert that defines the order? If yes you can use that of course later on too if you select from the table you INSERTed into.
    – sticky bit
    Dec 31, 2020 at 11:57
  • 1
    "same set of data for different rows" indicates a problem with your database design. Why would you want the same information multiple times?
    – Gerard H. Pille
    Dec 31, 2020 at 15:48
  • let me update the question with more clear expectations. Thanks and wishing you all a Happy new year
    – vikrant rana
    Jan 1, 2021 at 5:04

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy