Search code examples
mysqlsqldateselectgreatest-n-per-group

SQL - How to retrieve 5 most recent comments for each user

I have a table called 'user_text'

| id | user_id |        date         |         text         |
|----|---------|---------------------|----------------------|
| 1  |    4    | 07/01/2019 10:04:11 | This is a test       |
| 2  |    9    | 19/11/2018 09:43:00 | Here's another test  |
| ...|         |                     |                      |

What I need to do is to select the 5 most recent (field 'date') entries for each user_id

I've searched a lot about it and it seems that somehow I need a subquery but I can't find the right combination.

Solution

  • In MySQL 5.x, one option uses a correlated subquery:

    select u.*
from user_text u
where (
    select count(*)
    from user_text u1
    where u1.user_id = u.user_id and u1.date >= u.date
) <= 5