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
