Ctrlex
Ctrlex

Reputation: 61

Group Concat the max id in the query

How can i group_concat the selected max id in the query.

example:

select group_concat(max(id) SEPARATOR ',') from attempts group by user, attempt having count(*) > 1; 

im having an error code

1111: invalid use of group function

Upvotes: 0

Views: 989

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522824

One option is to use a subquery:

SELECT GROUP_CONCAT(id)
FROM
(
    SELECT MAX(id) AS id
    FROM attempts
    GROUP BY user
) t;

The subquery finds all max id values for users in your table. Then, we roll them up into a CSV string. Note that if you wanted only distinct id values then you could have done:

SELECT GROUP_CONCAT(DISTINCT id)

Upvotes: 1

Related Questions