Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

List the EMPLOYEES who are senior to king

Solved Go to solution

List the EMPLOYEES who are senior to king

Rising Star

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: List the EMPLOYEES who are senior to king

@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;

View solution in original post

1 REPLY 1
Highlighted

Re: List the EMPLOYEES who are senior to king

@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;

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here