4

I have an employee table where the fields are:

first_name, last_name, hire_date, salary, department_id, department_name, and so on.

I am asked to find most earliest hired employees. I know functions like max, sysdate, and months_between, but I don't know how to use it here?

I was thinking that this problem could be solved like this:

select e.first_name,max(e.hire_date)
from employees e

but it generates the following error:

 oRA-00937: not a single-group group function
    00937. 00000 -  "not a single-group group function"
    *Cause:    
    *Action:
    Error at Line: 1 Column: 7

So please give me hint what will be correct form?

competent_tech
  • 44,465
  • 11
  • 90
  • 113

5 Answers5

9

To find the earliest hired employee:

 SELECT * FROM
 (
     SELECT * 
     FROM  employees
     ORDER BY hire_date
 )
 WHERE rownum = 1;

If you want the top 10 earliest hired employees:

 SELECT * FROM 
 (
   SELECT * 
   FROM  employees
   ORDER BY hire_date
 )
 WHERE rownum <= 10;

This is using Oracle's pseudocolumn ROWNUM

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • The original question implies confusion about the difference between MAX and MIN. It's worth noting that to get the earliest date it's `ORDER BY hire_date ASC` (the ASC, meaning ascending, is implied in the example above) or to get the latest date it would be `ORDER BY hire_date DESC` – wweicker Nov 24 '11 at 22:03
  • How would you handle the situation where you have an indeterminate number of employees that were hired on the same (earliest) date? I believe that case is where my solution works better – Jake Feasel Nov 24 '11 at 22:29
4

It'll be min for earliest, not max.

Alternate route to order by and rownum solution

select min(hire_date) as earliest_hire_date from employees

That gets the earliest hire date. Call it a subquery and join back to employees to get rest of information you need

 select e.*, earliest_hire_date
 from (select min(hire_date) as earliest_hire_date from employees) a
 inner join employees e on e.hire_date = a.earliest_hire_date

Inner join functions as a filter here. Advantage to this method is if you have multiple employees hired on the same day, it will bring back multiple rows...one for each employee hired on that date.

If you're more comfortable without the inner join, this is equivalent:

select e.*, earliest_hire_date
from (select min(hire_date) as earliest_hire_date from employees) a, employees
where e.hire_date = a.earliest_hire_date
Twelfth
  • 7,070
  • 3
  • 26
  • 34
3
select * from employee 
where 
not exists 
(select 1 from employee e2 where e2.hire_date < employee.hire_date)
Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • thanks guys but here is one problem i have not studed yet rownum and subselect,so could i do otherwise? –  Nov 24 '11 at 21:46
  • Top 1 works great if you can be sure there is only one record that has the minimum hire date. If there are multiple with the same date, you'll need something like this. – Jake Feasel Nov 24 '11 at 22:16
  • Actually, your last comment is right. I read the question as "minimum date" only, but re-reading notice most earliest hired **employees**. I'll delete my first comment and stand corrected. :) +1. – Ken White Nov 24 '11 at 23:14
2

An analytic could work as well:

SELECT emp_name, hire_date
  FROM (SELECT first_name, hire_date
             , dense_rank() OVER (ORDER BY hire_date) rnk
          FROM employees e)
 WHERE rnk = 1;
DCookie
  • 42,630
  • 11
  • 83
  • 92
0

This will also work: If you want to display the data along with employee name and hiredate without using joins

select emp_name as ELDER_EMPLOYEE,hire_date 
from employees 
where hire_date=(select min(hire_date) from employees)
P.Sharan
  • 29
  • 4