CODEWITHSUNDEEP
mysqlsqldatabasegreatest-n-per-group
user443917
user443917

Reputation:

Get all actions of the last-three users for each post

In a last Question, i asked about geting all actions of the last three users from a history table that stores all actions done by users on deferments posts, now what i want is to get the same thing but for each post.

all actions of donne by the last-three users for each posts

history table
     id     | post_id | action    |  user_id
     1      |  5      | 1         |  3
     1      |  23     | 2         |  1
     2      |  24     | 2         |  6
     3      |  34     | 1         |  7
     4      |  35     | 1         |  1
     5      |  36     | 1         |  1
     6      |  23     | 2         |  3
     7      |  24     | 2         |  1
     8      |  23     | 1         |  4
     9      |  24     | 1         |  5
     10     |  24     | 1         |  1
     11     |  23     | 1         |  2
     12     |  23     | 4         |  1

thanks and sorry if it seem to be a duplicate post

Upvotes: 0

Views: 78

Answers (2)

sgeddes
sgeddes

Reputation: 62841

I think this will work:

SELECT  a.user_ID, a.post_id, a.action
FROM    tableName a
        INNER JOIN
        (
            SELECT  DISTINCT 
              @curRow:=IF(@prevRow=post_Id,@curRow+1,1) rn,
              user_ID, 
              Post_Id, 
              @prevRow:=Post_Id
            FROM    (
                SELECT DISTINCT Post_Id, User_Id
                FROM TableName
                ORDER   BY Post_Id, ID DESC 
              ) t  
              JOIN (SELECT @curRow:= 0) r

        ) b ON a.post_id = b.post_id AND a.user_id = b.user_id
WHERE b.rn <= 3
ORDER   BY a.post_id, a.User_ID

And the Fiddle.

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Coudl this be what you are looking for?

Code:

SELECT  a.user_ID,
group_concat(a.post_id),
group_concat(a.action)
FROM    tableName a
        INNER JOIN
        (
            SELECT  DISTINCT user_ID
            FROM    tableName   
            ORDER   BY ID DESC
            LIMIT   3
        ) b ON a.user_ID = b.user_ID
group by a.user_id
ORDER   BY a.User_ID;


| USER_ID | GROUP_CONCAT(A.POST_ID) | GROUP_CONCAT(A.ACTION) |
--------------------------------------------------------------
|       2 |                       7 |                      3 |
|       3 |                   5,5,4 |                  1,2,5 |
|       6 |                       7 |                      2 |

Upvotes: 0

Related Questions