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;
