Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005

    Red face Unanswered: More complicated use of aggregates...

    Greetings all!
    I am stuck with something at the moment, and that added to the cold thats got me in its icy grip is really batting my head
    Basically I have a table of records:
    Record_ID (id) | Employee_ID (pin) | Record_type (msgtype) | record_date | record_time (logtime)
    What I am looking for is, for each employee_ID on a specific day, the Record_ID of the earliest record of type '5'. I've only got one date in there at the moment so thats not such a problem. My attempt was:

    SELECT combined.ID
    FROM combined
    HAVING min(logtime);
    but that didnt work, complaining that is not in the group by.

    Can anyone provide any suggestions?


    EDIT: Forgot to mention, I'm using MS Access....

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    Perhaps something like this (or its variations - I'd say that interesting part is the use of a subquery):
    select c.employee_id, c.record_id
    from combined c
    where c.logtime = (select min(c1.logtime) 
                       from combined c1
                       where c1.employee_id = c.employee_id
      and c.record_type = 'S'                  
    group by c.employee_id, c.record_id;

  3. #3
    Join Date
    May 2005
    Cool, I'm giving that a go so I'll let you know how it works out. It's gotta go through about just under a million records, so its taking a while!!!

Posting Permissions

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