CODEWITHSUNDEEP
sqlhive
user2531569
user2531569

Reputation: 619

hive sql query to merge columns

I have run both queries below on the same table with different filtering criterion:

hive> select * from mailing1 where layer ='db_gold';
OK
db_gold 400
db_gold 500
db_gold 600

hive> select * from mailing1 where layer ='db_insight';
OK
db_insight  100
db_insight  200
db_insight  300

I am trying to combine both queries and generate an output like:

db_gold 400 db_insight 100
db_gold 500 db_insight 200
db_gold 600 db_insight 300

I already tried to "union" both the tables as below, with no luck:

select * from mailing1 where layer ='db_insight' 
 union 
select * from mailing1 where layer ='db_gold';
db_gold         400
db_gold         500
db_gold         600
db_insight      100
db_insight      200
db_insight      300

Upvotes: 1

Views: 438

Answers (1)

Iskuskov Alexander
Iskuskov Alexander

Reputation: 4375

It looks like JOIN operator is necessary, but there is no column for join. You can use row numbers of tables for this purpose:

SELECT * FROM
    (SELECT row_number() OVER () rn, * FROM mailing1 WHERE layer = 'db_insight') t1
INNER JOIN 
    (SELECT row_number() OVER () rn, * FROM mailing1 WHERE layer = 'db_gold') t2
ON t1.rn = t2.rn;

Upvotes: 1

Related Questions