Kristian Kristian - 1 month ago 13
MySQL Question

SQL count rows where id = x and other max(id) grouped on the same table

I need to count rows in one table where the field event_id = 17 and their isn't any other record in the table with higher g_event_id and the same value for prim_cid. My table contains events for phone calls. All events for the same call share the field "prim_cid". If the field event_id = 17 i know that the call is in queuee , but as soon its answered an new entry in table (same prim_cid) but other event_id occur. So i need to count the rows with event_id = 17 with has no later record with same prim_cid.

My table:

events:

g_event_id prim_cid event_id event_time
---------- -------- -------- ----------
1 1 16 2016-10-21 16:00:00
2 1 17 2016-10-21 16:00:01
3 1 18 2016-10-21 16:00:02
4 2 15 2016-10-21 16:01:01
5 2 17 2016-10-21 16:01:02
6 3 16 2016-10-21 16:02:01
7 3 17 2016-10-21 16:02:02
8 3 18 2016-10-21 16:02:38


From this i like to get the result:

1


(as only prim_cid 2 has no higher record on the field g_event_id)
Also i would like to do an select where i get the record like:

g_event_id prim_cid event_id event_time
---------- -------- -------- ----------
5 2 17 2016-10-21 16:01:02


In another sql select of course :)

Answer

You want to count prim_cids who "know" an event_id 17 but have no other g_event_id that is higher, than that of the 17-event. This translates directly into SQL:

SELECT COUNT(DISTINCT prim_cid)
FROM `events` AS seventeens
  LEFT JOIN `events` AS laters ON (seventeens.`prim_cid` = laters.`prim_cid` AND laters.`g_event_id` > seventeens.`g_event_id`)
WHERE seventeens.`event_id` = 17
  AND ISNULL(laters.`prim_cid`)

In order to fulfill your second statement you would use the following SELECT clause for the same statement:

SELECT DISTINCT seventeens.* 
FROM ...