Created 12-23-2015 09:11 AM
Hi guys,
The question is looking very simple.But i don't why i unable to get the solution.Think like we emp table similar to scott schema(oracle).I just want to write a query whose hiredate is more that hiredate of employee KING.
Here is the Query:
select ename,hiredate from emp e1 where e1.hiredate > (select hiredate from emp e2 where e2.ename='KING')
But unfortunately we have only support for 'IN' (Subqueries) in hive.How to achieve the requirement.
Created 12-23-2015 09:58 AM
There are two ways to do it which I can think of at this moment.
1. You can write a couple of Pig statements to accomplish this.
2. You can try Hive query like below: (I won't recommended it normally for performance issues as Hive will first do a full cartesian product in this query, then filter, but since one side of the join only has one row, that's not an issue here.)
select emp1.ename, emp1.hiredate from emp emp1 join (select hiredate from emp where emp.ename='KING') emp2 where emp1.hiredate > emp2.hiredate;
Created 12-23-2015 09:58 AM
There are two ways to do it which I can think of at this moment.
1. You can write a couple of Pig statements to accomplish this.
2. You can try Hive query like below: (I won't recommended it normally for performance issues as Hive will first do a full cartesian product in this query, then filter, but since one side of the join only has one row, that's not an issue here.)
select emp1.ename, emp1.hiredate from emp emp1 join (select hiredate from emp where emp.ename='KING') emp2 where emp1.hiredate > emp2.hiredate;