0

I have a scenario, for example:

with tmp as (select name from table1)
select * from table2 b 
where b.name=(select max(name) from tmp)

However, Hive can't recognize this syntax, so is there any legal syntax for this?

After search, I learnt it can use join to realize:

select table2.* from table2
join (select max(name) as name from tmp) t2
where table2.name = t2.name

but I don't want to use join, as the join will be very slow, I just want to regard it as a reference. Like in MySQL, you are able to set the result as a reference:

set @max_date := select max(date) from some_table;
select * from some_other_table where date > @max_date;

While Hive can achieve the effect that storing query result in shell. Check: HiveQL: Using query results as variables

Can Hive support such feature in SQL mode?

DennisLi
  • 3,915
  • 6
  • 30
  • 66

1 Answers1

0

In Hive you can achieve it as below:

select * from table2 b 
where b.name=(select max(name) from table1)

Other way : You can also create temporary table in hive which will help to replicate your Oracle query above.

CREATE TEMPORARY TABLE tmp AS SELECT name FROM table1;
SELECT * FROM table2 b WHERE b.name=(SELECT max(name) FROM tmp);
Saghir Hussain
  • 101
  • 1
  • 3