Thread: Find Two Longest Serving Employees

1. Registered User
Join Date
Oct 2004
Posts
12

Unanswered: Find Two Longest Serving Employees

Hello
I have to find two longest serving employees for each job in this table
employees(empname,job,hiredate). how can I do it? thanx.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
How do you know the termination date for each employee? Without that, I can't figure out how to compute their length of service.

Did the instructor give you a more detailed problem specification, or was this all that they gave you?

-PatP

3. Registered User
Join Date
Oct 2004
Posts
12
I don't need termination date. I need to find out which two current employees are working longer than the others.

4. Registered User
Join Date
Aug 2004
Posts
330
Assuming the employee table is only current employees, try:

SELECT EMPNAME, HIREDATE
FROM EMPLOYEES A
WHERE 2 > (SELECT COUNT(*) FROM EMPLOYEES B
WHERE A.HIREDATE > B.HIREDATE);

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
urquel: for each job

6. Registered User
Join Date
Aug 2004
Posts
330
That's simple:

SELECT EMPNAME, JOB, HIREDATE
FROM EMPLOYEES A
WHERE 2 > (SELECT COUNT(*) FROM EMPLOYEES B
WHERE A.HIREDATE > B.HIREDATE AND A.JOB = B.JOB);

7. Registered User
Join Date
Oct 2004
Posts
12
Originally Posted by urquel
That's simple:

SELECT EMPNAME, JOB, HIREDATE
FROM EMPLOYEES A
WHERE 2 > (SELECT COUNT(*) FROM EMPLOYEES B
WHERE A.HIREDATE > B.HIREDATE AND A.JOB = B.JOB);
Thanx a lot!!!
That's what I needed!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•