Kristian Kristian - 1 month ago 10
MySQL Question

MYSQL JOIN and get full row based on MAX

Really tried to search for a solution to this, but I can't get it to work.
I have 2 tables in Mysql

event_parties
and
events
.

event_parties:
g_event_id agent_id
---------- --------
2917 2
2918 2
2919 2
3067 3
3078 3

events:
g_event_id event_id event_time
---------- -------- ----------
2917 29 2016-10-19 15:24:25
2918 31 2016-10-19 15:24:28
2919 30 2016-10-19 15:29:46
3067 29 2016-10-20 15:33:46
3078 30 2016-10-21 15:29:46


I need an JOIN between these two tables with
g_event_id
as the
ID.

I need all fields in table
events
and I need the row with highest
g_event_id
or with highest
event_time
.

Like this:

agent_id g_event_id event_id event_time
-------- ---------- -------- ----------
2 2919 30 2016-10-19 15:29:46
3 3078 30 2016-10-21 15:29:46


Been struggling with this for several days :(

/ Kristian

Answer

For just the one highest: ORDER BY g_event_id DESC, event_time DESC LIMIT 1

UPDATE:

for highest per something, you need a double join. first have all in one table, and then join in later, and the one row that don't have a later evetn, is the latests event.

for g_event_id as definition of later

SELECT event_parties.agent_id, event_parties.g_event_id, event_id, event_time
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
GROUP BY event_parties.agent_id