|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 4th, 2004, 09:53 AM
|
||||
|
||||
|
problem getting first & last employee hired
Hi,
I can't form the appropriate query for this task: Display the department number and name of all departments and the full name of the employee(s) hired first and last in each department. The tables Im working with and their specs are as follows: Table Employees ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) Table Departments ------------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) Your help is greatly appreciated.
|
|
#2
November 4th, 2004, 10:15 AM
|
|||
|
|||
|
Hi Warr
Try This SQL> ED Wrote file afiedt.buf 1* SELECT DEPTNO,MIN(HIREDATE) FIRST,MAX(HIREDATE) LAST FROM EMP GROUP BY DEPTNO SQL> / DEPTNO FIRST LAST ---------- --------- --------- 10 09-JUN-81 23-JAN-82 20 17-DEC-80 23-MAY-87 30 20-FEB-81 03-DEC-81 This Query I Wrote On the default emp,dept table.make necessary changes... Happy Coding sasi242@yahoo.com
|
|
#3
November 4th, 2004, 10:21 AM
|
||||
|
||||
|
well ummm... I NEED THE NAMES of the employees hired first and last in each department
Also, I need the Department name which is found in the departments table
|
|
#4
November 4th, 2004, 11:50 AM
|
|||
|
|||
|
Code:
select d.department_Id,
d.department_name,
ef.first_Name,
ef.last_name,
el.first_name,
ef.first_name
from departments D ,
employees ef,
employees el
where d.department_id = ef.department_id
and d.department_id = el.department_id
and ef.hire_date in (
select min(hire_date) from employees e
where e.department_id = ef.department_id)
and el.hire_date in (
select max(hire_date) from employees e
where e.department_id = el.department_id)
|
|
#5
November 4th, 2004, 12:25 PM
|
||||
|
||||
|
This does not work. For some departments I get more rows then there are employees in that department.
Also, this is old syntax (8i). I am required to write queries in the new iSQL Plus 1999 syntax! Thanks for trying though 
|
|
#6
November 4th, 2004, 01:06 PM
|
|||
|
|||
|
I tested it with some sample data and it gave the expected result. Could you show your sample data (in the form of insert statements).
I don't understand why you are not allowed to use that syntax. It is still standard SQL.
|
|
#7
November 4th, 2004, 01:06 PM
|
|||
|
|||
|
Code:
SELECT empno, ename, hiredate, deptno
FROM emp
WHERE hiredate IN (SELECT max(hiredate) FROM emp GROUP BY deptno
UNION
SELECT min(hiredate) FROM emp GROUP BY deptno)
ORDER BY deptno
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > problem getting first & last employee hired |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
