Select last row for a certain group

Posted on

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

Leave a Reply

Your email address will not be published. Required fields are marked *