Question :
I have two tables:
Table users
columns | type | notes
--------|--------|-------
id | bigint | PK
... | ... | ...
Table events
columns | type | notes
-----------|---------|------
id | bigint | PK
user_id | bigint | FK(`users`.`id`)
event_name | varchar | ...
... | ... | ...
Example of Table events
id | user_id | event_name | ...
---|---------|------------|-----
1 | 1 | A | ...
2 | 1 | B | ...
3 | 2 | F | ...
4 | 3 | C | ...
5 | 1 | A | ...
6 | 2 | A | ...
I want get the latest event of a user (e.g: user_id
is 1
). So far, I found two solutions:
SELECT events.*
FROM events
WHERE events.id = (SELECT MAX(events.id) FROM events WHERE (events.user_id = 1));
and
SELECT events.*
FROM events
WHERE events.user_id = 1
ORDER BY events.id DESC
LIMIT 0, 1;
Which one is the better solution for this case? Or maybe, is there any better solution?
FYI, I use MySQL 5.6.19 and InnoDB is the db engine.
Answer :
There would be marginal difference if any between both the queries, however, the second query is much simpler, so if it were me, I would prefer the second one!
Another way to do this would be:
SELECT MAX(events.id), events.user_id, events.event_name
FROM events
WHERE events.user_id = 1
Oh, and if you want to get the results of all user collectively, this query would help:
SELECT MAX(events.id), events.user_id, events.event_name
FROM events
GROUP by events.user_id
The output would be something like:
-------------------------------------
MAX(events.id) | user_id | event_name
-------------------------------------
5 | 1 | A
6 | 2 | F
4 | 3 | C