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;