find the most early hired employees in every department

<>

This question already has an answer here:

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.

This query

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.


Need Your Help

automator enable/disable/delete action in run applescript

applescript automator

I have an automator workflow with a Run Applescript action. Is it possible to enable/disable/delete another automator action in my Run Applescript? See my previous question for more details.

How to create id in element in template when I have passed parameter?

python html django django-templates

How to create id in element in template when I have passed parameter ?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.