CODEWITHSUNDEEP
sqloracle-databaseora-00937
user466534
user466534

Reputation:

Select earliest hired employee from employee table

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?

Upvotes: 4

Views: 35753

Answers (5)

P.Sharan
P.Sharan

Reputation: 29

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)

Upvotes: 0

DCookie
DCookie

Reputation: 43533

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;

Upvotes: 2

Twelfth
Twelfth

Reputation: 7180

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

Upvotes: 4

p.campbell
p.campbell

Reputation: 100587

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

Upvotes: 9

Jake Feasel
Jake Feasel

Reputation: 16955

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

Upvotes: 3

Related Questions