How get max value from result of group_concat mysql [closed]

Question

I am trying to get the max value from the result of group_concat.

Here is sample data, and what I have tried,

+----+---------+--------------------------+
| id | user_id | comment                  |
+----+---------+--------------------------+
|  1 |      80 | I don't need it any more |
|  2 |     222 | Don't need this account  |
|  3 |      80 | I have an other account  |
|  4 |      80 | The other comment        |
|  5 |     222 | some x                   |
+----+---------+--------------------------+
4 rows in set (0.01 sec)

I tried the following query,

mysql> select max(group_concat(id  SEPARATOR ' ')), user_id from userapp_accactivitylog;

But it is giving me error

ERROR 1111 (HY000): Invalid use of group function

With group_concat(id) We will get the result 1,2,3,4,5 . My requirement is that I want to select greatest number from the result I get with group_concat.

I hope you understand what I am looking for, please let me know if there is a way to achieve the following result:

+----+---------+--------------------------+
| id | user_id | comment                  |
+----+---------+--------------------------+
|  5 |     222 | some x                   |
|  4 |      80 | The other comment        |
+----+---------+--------------------------+
2 rows in set (0.01 sec)

I am trying to group by user_id and I want to fetch the largest number of record in the id column (i.e. 5 in this case).

Answer 1

This should get what you want:

select
  user_id,
  max(id) as id,
  substring_index(group_concat(comment order by id desc SEPARATOR '|'), '|', 1)
from
  userapp_accactivitylog
group by user_id;