find the most early hired employees in every department

Question

I have the following code for finding most early hired employees in every department

select  d.department_name,e.first_name || ' '||  e.last_name,e.hire_date,min(e.hire_date)
      from employees e join departments d
      on e.department_id=d.department_id
   group by d.department_name,e.hire_date

but it shows me following error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 13 Column: 26

please help me what is wrong with this code?

What are you trying to do exactly? GROUP BY creates summary rows, but you are trying to pull individual records. — BNL, Dec 01 '11 at 15:48

score 2 · Answer 1 · answered Dec 01 '11 at 16:00

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.

Justin Cave · Answer 2 · 2011-12-01T16:24:25.767

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

Vincent Savard · Answer 3 · 2011-12-02T01:18:27.510

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.

Ed B · Accepted Answer · 2011-12-01T16:28:02.047

1

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

edited Dec 01 '11 at 16:28

answered Dec 01 '11 at 16:03

Ed B

6,028
3
26
35

This works other than the fact that in Oracle, the string concatenation operator is `||`, not `+` when you're creating the `EmployeeName` column. However, it is generally less efficient than the analytic function solution since it requires hitting the `employees` table twice rather than once using the analytic function. This may not be terribly significant if the tables are small but it may add up if the tables grow or if the query is executed multiple times. Personally, I also tend to find the analytic function approach to be easier to read and maintain as well. – Justin Cave Dec 01 '11 at 23:47

score 0 · Answer 5 · answered Dec 01 '11 at 15:49

0

select  d.department_name,e.first_name || ' '||  e.last_name,min(e.hire_date) 
      from employees e join departments d
      on e.department_id=d.department_id
   group by d.department_name,e.first_name || ' '||  e.last_name

answered Dec 01 '11 at 15:49

triclosan

5,578
6
26
50

1

This will return the earliest hire date for *every* employee - not the whole department. – Dec 01 '11 at 16:18

find the most early hired employees in every department

5 Answers5