Select the earliest employee from the employee table

Question

I have a staff table where there are fields:

first_name, last_name, hire_date, salary, department_id, username, etc.

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

I thought this problem could be solved as follows:

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 a hint, what will be the correct form?

Answer 1

It will be minimum, not maximum.

Alternative route for ordering and solving rownum

 select min(hire_date) as earliest_hire_date from employees 

This gets the earliest hiring date. Call it a subquery and join the staff to get the 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 

The functions of the inner join as a filter are here. The advantage of this method is that if you have several employees hired on the same day, they will return several rows ... one for each employee hired on that date.

If you're comfortable without an internal join, this is equivalent to:

 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 

Answer 2

To find the earliest employee:

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

If you want to get the 10 earliest employees:

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

This is using pseudocolumn Oracle ROWNUM

Answer 3

 select * from employee where not exists (select 1 from employee e2 where e2.hire_date < employee.hire_date) 

Answer 4

Analytics might also work:

 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; 

Answer 5

This will also work: If you want to display data with the employee name and hire without using associations

 select emp_name as ELDER_EMPLOYEE,hire_date from employees where hire_date=(select min(hire_date) from employees)