eliminate duplicate results

Hi,

Here is a brief description of a problem I'm currently experiencing:

We have a phone line that employees can call to report their status. There is no limit on how often they can call. Each time a call is completed a record is inserted into a table with the following info: the employees unique id, theire current status, and the location of an audio file should they have chosen to leave a message. When I query this table I'd like to only return the most recent call for each employee, disregarding all the rest.

So...

When I do a simple query like:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId

I get only one record, the most recent one, for each employee who has called. However as soon as I try to get another field I get one record for each different value in that field.

a second query:

SELECT MAX(DISTINCT r.ircDateStamp),
r.entityId,
r.ircStatus
FROM tblInboundRollCall r
WHERE (r.ircLineId = 13) AND (r.ircDateStamp BETWEEN startDate AND endDate)
GROUP BY r.entityId, r.ircStatus

This returns one record for each status value that each employee has.

I guess my question is how do I retrieve all of the fields in a record with the most recent date for each unique employee.

Thanks for the help.Read this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31147

And change MIN to MAX|||Thanks for the quick reply.

Which post in that thread am I looking at?|||Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect

Thanks Brett!