Sorting data in groups

Question

Dataset:

id  uid     activity    postid  
1   20      A           1
2   20      A           1
3   6       A           1
4   3       A           1
5   6       A           1
6   13      A           1
7   13      B           1
8   18      B           1
9   18      B           1
10  1       A           1

Current Results:

id  uid     uid_list        groupCount  activity    postid
9   18      18,13           3           B           1
1   20      1,13,6,3,20     7           A           1

Expected results:

id  uid     uid_list        groupCount  activity    postid
9   18      18,13           3           B           1
10  1       1,13,6,3,20     7           A           1

The query I have is:

SELECT
    id,
    uid,
    GROUP_CONCAT(DISTINCT uid ORDER BY id DESC) as uid_list,
    COUNT(*) as groupCount,
    activity,
    postid
FROM (
    SELECT *
    FROM `user_activities`
    ORDER BY id DESC) as tbl
GROUP BY
    activity, postid
ORDER BY
    id DESC

I want to group activityand postidhaving the result in descending order by id. And you want to have the latest idand uidfor each group. I do not understand why this query does not return the expected result.

Answer 1

From what I understand, the value is idincreasing. To get the latest values, you can use the aggregate function MAX().

, , GROUP_CONCAT() id.

uid id, .

SELECT
    a.id, b.uid, a.uid_list, a.groupcount, a.activity, a.postid
FROM (
    SELECT
        MAX(id) as id,
        GROUP_CONCAT(DISTINCT uid ORDER BY id DESC) as uid_list,
        COUNT(*) as groupCount,
        activity,
        postid
    FROM user_activities a
    GROUP BY
        activity, postid
    ) a
    INNER JOIN user_activities b ON a.id = b.id

Answer 2

, group_concat()/substring_index():

SELECT MAX(ID) as id,
       SUBSTRING_INDEX(GROUP_CONCAT(uid ORDER BY ID DESC), ',', 1) as uid,
       GROUP_CONCAT(DISTINCT uid ORDER BY id DESC) as uid_list,
       COUNT(*) as groupCount,
       activity, postid
FROM user_activities ua
GROUP BY activity, postid
ORDER BY id DESC;

, , group_concat() . , , , ( uid).