I am running a below query in
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
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
ORDER BYin 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 BYeach time.
SELECTyou insert that defines the order? If yes you can use that of course later on too if you select from the table you