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:
GROUP BY combined.pin
but that didnt work, complaining that combined.id is not in the group by.
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;