Select earliest hired employee from employee table

Question

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?

Answer 1

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