Sorted 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:

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 by activity and postid while having the result in descending order by id. And want to have the latest id and uid for every group. I don't understand why this query doesn't return the expected output.

Kamil Gosciminski · Accepted Answer · 2016-02-21T17:51:08.703

2

From what I understand id value is increasing. To get the latest values you could use an aggregate function MAX().

Also, your inner query with ordering is unnecessary because the engine has to sort the resultset by id anyways when building result for GROUP_CONCAT().

To retrieve uid for a particular id column you need to self join to the same table.

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

edited Feb 21 '16 at 17:51

answered Feb 21 '16 at 17:43

Kamil Gosciminski

16,547
8
49
72

No dude, I can't get `MAX(uid)`, I need latest `uid` (latest by `id`). And no `uid` is not increasing, it is FK from another table. – Shaharyar Feb 21 '16 at 17:45
My bad. Updated the answer. – Kamil Gosciminski Feb 21 '16 at 17:49
Well its a nice approach and solves the problem as well. But I am curious why it doesn't sort the rows before grouping? Even after my subquery approach to sort them? – Shaharyar Feb 21 '16 at 17:54
This is probably because you are ordering by a column that is not inside `group_concat`. Please try changing it to: `DISTINCT uid ORDER BY uid DESC`. You don't need to order it by `id` anyways if I understand correctly. – Kamil Gosciminski Feb 21 '16 at 18:22
No no it will mess up everything :) I need to sort it by `id`. Anyways thanks for the smart solution. – Shaharyar Feb 21 '16 at 18:25
It seems in the output that `uid` values inside `uid_list` are actually sorted according to `id DESC`. I have no way of testing it atm, but looking at your question it seems correct. – Kamil Gosciminski Feb 21 '16 at 18:29
Yes it is correct, that's why I accepted it. I asked for why my query didn't workout, never said your answer is incorrect. – Shaharyar Feb 21 '16 at 18:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104108/discussion-between-consider-me-and-shaharyar). – Kamil Gosciminski Feb 21 '16 at 18:36

score 0 · Answer 2 · answered Feb 21 '16 at 17:59

0

Probably the simplest method is the group_concat()/substring_index() trick:

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;

There are some limitations to this approach, in the sense that GROUP_CONCAT() has a maximum length for the intermediate value. Typically the default is sufficient, but you might need to change that value if many, many rows match each group (and you already have this issue for the list of uids anyway).

answered Feb 21 '16 at 17:59

Gordon Linoff

1,242,037
58
646
786

What is the default length for `GROUP_CONCAT()`? – Shaharyar Feb 21 '16 at 18:01
@Shaharyar . . . http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len. I emphasize that this affects your existing `group_concat()` as well as the second one proposed here. – Gordon Linoff Feb 21 '16 at 18:38

Sorted data in groups

2 Answers2