More complicated use of aggregates...

shiv_379 · #1 (**permalink**) 12-02-05, 06:14

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:

Code:

SELECT combined.ID
FROM combined
GROUP BY combined.pin
HAVING min(logtime);

but that didnt work, complaining that combined.id is not in the group by.

Can anyone provide any suggestions?

Thanks!
~Shiv

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

Littlefoot · #2 (**permalink**) 12-02-05, 06:35

Perhaps something like this (or its variations - I'd say that interesting part is the use of a subquery):

Code:

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;

shiv_379 · #3 (**permalink**) 12-02-05, 06:42

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!!!
~T

Internet Services

Web Development

Digital Marketing

Consumer Tech