select e1.*
from employee_info e1
where e1.deptno = #deptno"
and e1.eff_date =
(select max(e2.eff_date)
from employee_info e2
where e2.empno = e1.empno)
i think...
-----Original Message-----
From: Ang�l Stewart [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 07, 2000 12:18 PM
To: [EMAIL PROTECTED]
Subject: SQL Question: Select a list of most recent records from a table
that stores User Histories,for ALL users.
Okies you SQL gurus out there :-)
I have a table called Employee Info.
It contains Employee ID, Department ID, and Effective Date as well as other
things.
ANyways, I am allowing the user to select a list of employees by Department
Date, from this table.
However, I only want to return ONE record PER Employee ID, and that record
must be the most RECENT according to the Effective Date.
So..if I have.
1111 Bond,James 11001 01/01/2000 5dzb1
1111 Bond,James 11001 03/07/2000 9tyu6
1111 Nibble,Neeby 11001 01/06/2000 9iogj
1002 Joans,Avery 12001 01/01/1999 0iuyt
The system should only return :
1111 Bond,James 11001 03/07/2000 5dzb1
1111 Nibble,Neeby 11001 01/06/2000 9iogj
If the user does a search by department for department number 11001.
I am hoping that someone is able to tell me how to do this very quickly, and
that its quite easy to do :-)
Thanks!
ciao!
*waves merrily*
-Gel
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message
to [EMAIL PROTECTED] with 'unsubscribe' in the body.
