List the EMPLOYEES who are senior to king

[#1]

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.

[#2 · Accepted solution]

@Suresh Bonam

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;