CODEWITHSUNDEEP
mysqlsql
Nick Shears
Nick Shears

Reputation: 1133

MYSQL retrieving user activity where the same user_id can appear a maximum of 3 times

I'm retrieving rows from an user activity table like so 

SELECT user_id, type, source_id FROM activity ORDER BY date DESC LIMIT 5

But I don't want the activity feed to be able to be clogged up by the same user, so I want to be able to retrieve a maximum of 3 rows out of 5 that contain the same user_id.

Any ideas how I could do this? Thanks :)

Upvotes: 2

Views: 72

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You can try this:-

SELECT user_id, type, source_id
FROM activity
WHERE 3 > (
SELECT count( * )
FROM activity AS activity1
WHERE activity .user_id = activity1.user_id
AND activity.user_id > activity1.user_id)
ORDER BY activity.user_id DESC
LIMIT 5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Here is a "traditional" way, where you first enumerate the user idsand use this information as a filter:

SELECT user_id, type, source_id
FROM (select a.*,
             @rn := if (@user_id = user_id, @rn + 1, 1) as rn,
             @user_id := user_id
      from activity a cross join
           (select @rn := 0, @user_id := -1) const
      order by user_id
     ) a
WHERE rn <= 3
ORDER BY date DESC
LIMIT 5;

Upvotes: 1

Related Questions