MYSQL Select group by and limit per group where created_at is within this week

Question

Below is my database table.

id    user_id    group_id    created_at 
 1    1          1           2016-09-29
 2    1          2           2016-10-02
 3    1          3           2016-10-02
 4    1          4           2016-10-03
 5    1          5           2016-10-03

 6    2          1           2016-01-02
 7    2          2           2016-01-02
 8    2          3           2016-01-03

 9    3          1           2016-09-29
10    3          2           2016-09-30
11    3          3           2016-10-02
12    3          4           2016-10-02
13    3          5           2016-10-03

Below is my sql query.

SELECT myTable.* 
FROM myTable 
INNER JOIN (
    SELECT id 
    FROM myTable 
    GROUP BY id 
) group_max 
ON myTable.id = group_max.id 
WHERE YEARWEEK(myTable.add_time) = YEARWEEK(NOW()) 
ORDER BY myTable.user_id ASC, myTable.add_time ASC

With the above query I get the following result.

id    user_id    group_id    created_at 
 4    1          4           2016-10-03
 5    1          5           2016-10-03
 2    1          2           2016-10-02
 3    1          3           2016-10-02

 8    2          3           2016-01-03
 6    2          1           2016-01-02
 7    2          2           2016-01-02

13    3          5           2016-10-03
11    3          3           2016-10-02
12    3          4           2016-10-02

How to select the last 2 recent records (within this week) group by user_id as the following.

id    user_id    group_id    created_at 
 5    1          5           2016-10-03
 4    1          4           2016-10-03

 8    2          3           2016-01-03
 7    2          2           2016-01-02

13    3          5           2016-10-03
12    3          4           2016-10-02

Answer 1

Below is the working sql query.

SELECT a.* 
FROM myTable AS a 
WHERE (
    SELECT COUNT(*) 
    FROM myTable AS b 
    WHERE b.user_id = a.user_id AND b.created_at >= a.created_at
) <= 3 
AND YEARWEEK(a.created_at) = YEARWEEK(NOW()) 
ORDER BY a.user_id ASC, a.created_at ASC