Hive Query not working

[#1]

> 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.

[#2]

@sanjeevan mahajan

Can you try this out:

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) from new2_salaries s, employees e
WHERE s.emp_no = e.emp_nosel
GROUP BY s.emp_no

[#3]

@Daniel Kozlowski : It's still gives an exception "Expression not in Group By key emp_no"

[#4]

@sanjeevan mahajan

How about this:

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) from new2_salaries s, employees e
WHERE s.emp_no = e.emp_nosel
GROUP BY s.emp_no, s.from_date

[#5]

@Daniel Kozlowski Still not

[#6]

@sanjeevan mahajan

I have made quite few attempts - the ones that actually should work. Similar to

SELECT j.* 
FROM new2_salaries j 
WHERE j.from_date = 
(SELECT MIN(j2.from_date) FROM new2_salaries j2 WHERE j.empl_no = j2.empl_no);

Unfortunately, this is not working due to an existing bug: https://issues.apache.org/jira/browse/HIVE-8960

[#7]

@Daniel Kozlowski Please see attached screen grab screen-shot-2016-09-02-at-225412.png

[#8]

It doesn't work because, to quote the related wiki page: When using group by clause, the select statement can only include columns included in the group by clause, and aggregate functions on other columns. So, your query will work if you remove "group by" and "min".

[#9 · Accepted solution]

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

[#10]

@ Constantin Stanca : Thanks a lot for the help but Its is still giving semantic exception Invalid table alias or column reference 's': possible column names are emp_no, salary, from_date, to_date