CODEWITHSUNDEEP
mysqlsqldateselectgreatest-n-per-group
naspy971
naspy971

Reputation: 1381

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.

Upvotes: 1

Views: 71

Answers (2)

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use row_number():

select t.*
from (select t.*, row_number() over (partition by user_id order by date desc) as seqnum
      from t
     ) t
where seqnum <= 5;

Upvotes: 1

Related Questions