Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read this thread

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

    And change MIN to MAX
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2003
    Posts
    41
    Thanks for the quick reply.

    Which post in that thread am I looking at?

  4. #4
    Join Date
    Feb 2003
    Posts
    41

    Talking

    Just got it working. With a little tweaking of the second last post in the linked thread everything is perfect

    Thanks Brett!

Posting Permissions

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