Two ideas . . . you might want to compare to check for speed differences, or
hold out for a better answer . . .
One:
select emp_id, emp_name, dept_id, max(effective_date) effective_date
from employees
group by emp_id, emp_name, dept_id
Depending on the size of the table and the way it is designed, etc., this
may or may not slow you down.
Two:
Sort the query (order by emp_id, effective_date desc) and then use your CF
to compare emp_id to the previous row and only output if it is different.
<cfoutput query="xxx">
<cfif xxx.currentrow eq 1 or xxx.emp_id neq xxx.emp_id[xxx.currentrow - 1]>
output stuff
</cfif>
</cfoutput>
Dan
-----Original Message-----
From: Ang�l Stewart [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 07, 2000 9:18 AM
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.
