find the most early hired employees in every department<>
This question already has an answer here:
- ORA-00979 not a group by expression 3 answers
Once you get the earliest hire date for each department, you can do an inner join to the employees table to see who you hired that date from that department.
Edit: Something like this should work.
select a.department_name, a.EarliestHireDate, b.first_name + ' ' + b.last_name AS EmployeeName from ( select min(e.hire_date) as EarliestHireDate, d.department_name, d.department_id from employees e join departments d on e.department_id=d.department_id group by d.department_name ) as a inner join employees as b on a.EarliestHireDate = b.hire_date and a.department_id = b.department_id
You will get more than 1 employee per department if more than one was hired that earliest hire day in that department
You are including non-aggregated, non-grouped fields within a grouped query.
Also, you are grouping by hire_date as well as department, so your query will return a row for every distinct hire_date within each department, instead of only returning one row per department (although this won't stop your query running, but it will stop it returning the desired results.)
Consider looking into either sub-queries or analytical functions to return the data you require.
The easiest approach is generally to use an analytic function here. The wrinkle, though, comes if there are ties. If there are two people in the same department that were hired on the same day, which isn't terribly unlikely, do you want to return both of them? Or do you want to specify a secondary criteria to break the tie? Or do you just want Oracle to randomly break the tie? And, if you're not looking for the top row but the top 3 or top 5, how do you treat the rows after the tie.
select department_name, employee_name, hire_date from ( select d.department_name, e.first_name || ' '|| e.last_name employee_name, e.hire_date, row_number() over (partition by d.department_name order by e.hire_date asc) rnk from employees e join departments d on e.department_id=d.department_id ) where rnk = 1
will return the employee with the earliest hire_date in each department by randomly breaking ties if two employees were hired on the same day. If you used either the rank or the dense_rank function rather than row_number, both rows would be returned. If you added an additional criteria to the order by in the analytic function, you could determine how to break the tie (for example by sorting on the employee's last name).
Such a query could not work. Why are you grouping by those columns? What are you trying to achieve by doing this? How can you traduct the query in words?
Before making the query, write it in English :
The most early hired employee is the employee that has the lowest (earliest) hire_date, for every department.
I am not going to give you the query because I sense this is a homework, but you should be able to go down from here.