Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive Query not working

avatar
Rising Star

> Not sure can use select min(from_date) clause

create table employees2_final stored as ORC tblproperties (‘orc.compress’=‘SNAPPY’) AS SELECT e.emp_no, e.birth_date,e.first_name, e.last_name, e.gender, select min(s.from_date) from new2_salaries s GROUP BY s.emp_no WHERE s.emp_no = e.emp_no as hire_date_new from employees e.

1 ACCEPTED SOLUTION

avatar
Super Guru

@sanjeevan mahajan

... and to add to what Predrag stated based on the documentation, the same is true for all other databases, including Oracle, PostgreSQL, etc. The query needs to be rewritten to achieve the expected result, first find the min(s.from_date) per s.emp_no and at the second step join with e.emp_no to retrieve the needed other fields, as lookups.

Try this:

SELECT e.emp_no, e.birth_date,e.first_name, e.last_name, e.gender, s.min_from_date
FROM employees e,
(SELECT emp_no, min(from_date) as min_from_date 
FROM new2_salaries
GROUP BY s.emp_no) s
WHERE s.emp_no = e.emp_no;

If any of the responses to your question addressed the problem don't forget to vote and accept the answer. If you fix the issue on your own, don't forget to post the answer to your own question. A moderator will review it and accept it.

View solution in original post

12 REPLIES 12

avatar

Hi @sanjeevan mahajan and @Constantin Stanca. There is a teeny tiny fix needed to Constantin's last query - the inner query needs to state "Group By emp_no" instead of "Group By s.emp_no". This snippet should work:

SELECT e.emp_no, e.birth_date,e.first_name, e.last_name, e.gender, s.min_from_date
FROM employees e,
(SELECT emp_no, min(from_date) as min_from_date 
FROM new2_salaries
GROUP BY emp_no) s
WHERE s.emp_no = e.emp_no;

By the way - going way back to your original query, this snippet should work as well. Note the changes - using double-quotes around the orc.compress parameters, ensuring that all selected (non-aggregated) columns are in the group by statement, and user the inner join syntax to explicitly call out your join and join clause. Personal opinion: It's cleaner to keep your join clauses and the where clauses separated... instead of having the join clauses stuffed into the where clause.

create table employees2_final 
stored as ORC 
tblproperties ("orc.compress"="SNAPPY") 
AS 
SELECT e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender, 
       min(s.from_date) as `hire_date_new`
from new2_salaries s inner join employees e
   on s.emp_no = e.emp_no 
GROUP BY e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender; 

avatar
Super Guru

@bpreachuk

Thank you for catching my typo. I wrote the query while on the phone. I voted your addition. Thanks again.

avatar
Rising Star

@bpreachuk and @Constantin Stanca Thanks a lot for resolving this.