Heads up about the title, I am clueless how to word this accurately but I think it should be explaining what I am trying to achieve.
I have two tables, one for 'players' which have exactly one row per player and a second table consisting of 'events' regarding all players, these include various changes to the players account.
DESCRIBE PLAYERS;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| ORIGINAL_REGION | varchar(4) | NO | | NULL | |
| REGION | tinyint(4) | NO | | NULL | |
| ACCOUNT_ID | bigint(20) | NO | | NULL | |
| USERNAME | varchar(16) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
DESCRIBE EVENTS;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| ORIGINAL_REGION | varchar(4) | NO | | NULL | |
| ACCOUNT_ID | bigint(20) | NO | | NULL | |
| TIMESTAMP | bigint(20) | NO | | NULL | |
| PREVIOUS_NAME | varchar(16) | NO | | NULL | |
| NAME | varchar(16) | NO | | NULL | |
| RENAMED | tinyint(1) | NO | | NULL | |
| PREVIOUS_REGION | tinyint(4) | NO | | NULL | |
| REGION | tinyint(4) | NO | | NULL | |
| TRANSFER | tinyint(1) | NO | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
To get a list of all Players and any associated events I can left join events on players
SELECT * FROM PLAYERS P LEFT JOIN EVENTS E ON P.ACCOUNT_ID=E.ACCOUNT_ID AND P.ORIGINAL_REGION=E.ORIGINAL_REGION;
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 1 | EUW1 | 2592238782309408 | 1626896911435 | hawot2541 | XRO SCRIPTKID 2 | 1 | 3 | 3 | 0 |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 3 | EUW1 | 2592238782309408 | 1626897970705 | XRO SCRIPTKID 2 | XRO SCRIPTKID 2 | 0 | 3 | 2 | 1 |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 4 | EUW1 | 2592238782309408 | 1626898970705 | XRO SCRIPTKID 2 | sir hawloT | 1 | 2 | 4 | 1 |
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
My problem now is that I am unable to query this table to only include the LATEST event (based on the timestamp) for each Player.
The desired output would look like this:
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 4 | EUW1 | 2592238782309408 | 1626898970705 | XRO SCRIPTKID 2 | sir hawloT | 1 | 2 | 4 | 1 |
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+-----------------+-----------------+---------+-----------------+--------+----------+
I have tried multiple subqueries, and whatever what queries I was able to find online. Sadly none giving the results I need. The closest I got was using GROUP BY ACCOUNT_ID,ORIGINAL_REGION but this did not allow me to select the one with the most latest timestamp value.
SELECT * FROM PLAYERS P LEFT JOIN EVENTS E ON P.ACCOUNT_ID=E.ACCOUNT_ID AND P.ORIGINAL_REGION=E.ORIGINAL_REGION GROUP BY P.ACCOUNT_ID,P.ORIGINAL_REGION;
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
| ID | ORIGINAL_REGION | REGION | ACCOUNT_ID | USERNAME | ID | ORIGINAL_REGION | ACCOUNT_ID | TIMESTAMP | PREVIOUS_NAME | NAME | RENAMED | PREVIOUS_REGION | REGION | TRANSFER |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
| 2 | EUN1 | 4 | 123 | testuser | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | EUW1 | 3 | 2592238782309408 | hawot2541 | 1 | EUW1 | 2592238782309408 | 1626896911435 | hawot2541 | XRO SCRIPTKID 2 | 1 | 3 | 3 | 0 |
+----+-----------------+--------+------------------+-----------+------+-----------------+------------------+---------------+---------------+-----------------+---------+-----------------+--------+----------+
I have been trying for multiple hours now and hope someone here can tell me if this is even possible.
