CODEWITHSUNDEEP
mysqlsql
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 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: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a query that requires lots of self joins:

select hl.post_id, h.*
from history h join
     (select h.*, count(*) as NumLater
      from history h join
           history h2
           on h.post_id = h2.post_id and
              h2.id >= h.id
      group by h.id
      having NumLater <= 3
     ) hl
     on h.user_id = hl.user_id
order by hl.post_id

The inner query does a self join to calculate the number of history entries after each record in the same post. The join then joins this by user_id to the history table. This version does not eliminate duplicates. So, a user could be in the last-three set for two different posts.

Upvotes: 1

Related Questions