MYSQL self join get row based on MAX and having

Question

I need to COUNT rows from 2 tables and only get the rows with highest g_event_id if events.event_id IN (30, 31, 32, 33). Only take rows in account where events.event_id is 30-33.

SQL Fiddle: Fiddle

My tables:

 CREATE TABLE event_parties
 (`g_event_id` int, `agent_id` int)
;

INSERT INTO event_parties
(`g_event_id`, `agent_id`)
VALUES
(2917, '2'),
(2918, '2'),
(2919, '2'),
(3067, '3'),
(3078, '3'),
(3079, '1'),
(3082, '1'),
(3917, '2'),
(3918, '2'),
(3919, '2'),
(4067, '3'),
(4078, '3'),
(4079, '1'),
(5067, '3'),
(5078, '3'),
(5079, '1'),
(6067, '3'),
(6078, '3'),
(6079, '1'),
(7067, '3'),
(7078, '3'),
(7079, '1'),
(8067, '3'),
(8078, '3'),
(8079, '1')

;
CREATE TABLE events
(`g_event_id` int, `event_id` int, `event_time` datetime)
;

INSERT INTO events
(`g_event_id`, `event_id`, `event_time`)
 VALUES
(2917, '29', '2016-10-19 15:24:25'),
(2918, '31', '2016-10-19 15:24:28'),
(2919, '21', '2016-10-19 15:29:46'),
(3067, '29', '2016-10-20 15:33:46'),
(3078, '23', '2016-10-21 15:29:46'),
(3079, '29', '2016-10-20 15:34:46'),
(3082, '30', '2016-10-21 15:42:46'),
(3917, '29', '2016-10-19 15:24:25'),
(3918, '31', '2016-10-19 15:24:28'),
(3919, '21', '2016-10-19 15:29:46'),
(4067, '29', '2016-10-20 15:33:46'),
(4078, '23', '2016-10-21 15:29:46'),
(4079, '29', '2016-10-20 15:34:46'),
(5067, '29', '2016-10-20 15:33:46'),
(5078, '23', '2016-10-21 15:29:46'),
(5079, '29', '2016-10-20 15:34:46'),
(6067, '29', '2016-10-20 15:33:46'),
(6078, '23', '2016-10-21 15:29:46'),
(6079, '29', '2016-10-20 15:34:46'),
(7067, '29', '2016-10-20 15:33:46'),
(7078, '23', '2016-10-21 15:29:46'),
(7079, '29', '2016-10-20 15:34:46'),
(8067, '29', '2016-10-20 15:33:46'),
(8078, '23', '2016-10-21 15:29:46'),
(8079, '29', '2016-10-20 15:34:46')

;

The select is suppose to give me the status of an Callcenter Agent, i want to count how many agents (agent_id) in each state (event_id). As the table "events" is just events of the agents i only need to count the latest (with highest value) g_event_id of each agent_id and tricky part is that i only want to count where event_id IN (30, 31, 32, 32, 33). So basicly, select rows with highest g_event_id (and event_id IN (30, 31, 32, 33)) for each agent_id. I need an JOIN between these two tables with g_event_id as the ID. The field g_event_id is the key and only appears once. I need all fields in table events and I need the row with highest g_event_id or with highest event_time. Like this:

event_id   N_events
--------   ----------
    31         1
    30         1

I have this select so far:

SELECT event_id,
COUNT(events.event_id) AS N_events
FROM event_parties 
INNER JOIN events USING (g_event_id)
LEFT JOIN event_parties AS later_event
ON (later_event.agent_id = event_parties.agent_id 
  AND later_event.g_event_id > event_parties.g_event_id)
WHERE later_event.g_event_id IS NULL AND event_parties.agent_id != 0 AND     events.`event_id` IN (30,31,32,33)
GROUP BY events.event_id

Problem with this select above is that it will only give me the rows with highest g_event_id, i want to only select rows having events.event_id = (30, 31, 32, 33) and then count the rows with highest g_event_id

Been trying to use having after the GROUP (HAVING events.event_id IN (30,31,32,33)) without any success.

Answer 1

This query should give you your result:

select e.event_id, count(stats.agent_id) as N_count
from (
  select max(p.g_event_id) as g_event_id, p.agent_id
  from events e 
  join event_parties p
  on e.g_event_id = p.g_event_id
  where e.event_id in (30,31,32,33)
  group by p.agent_id
) as stats
join events e
on e.g_event_id = stats.g_event_id
group by e.event_id;

The inner query (stats) first retrieves the latest relevant status of each agent: it will get the largest g_event_id for each agent_id with an event_id in the given range (so at most one row for each agent).

It will then be joined with the events-table to retrieve the actual event_id for this g_event_id; then it counts the number of agents per event_id.

As worked out in the comments, this assumes that g_event_id is the primary key for both tables (but especially for events), and that the newest status is given by the largest g_event_id, not the event_time.