Skip to content
Advertisement

How to get max value from group by and groupconcat other field in mysql?

I tried to get the max value corresponding row and group_concat all the email address.

MySql table:

expected output:

I tried the following query:

but it gave wrong result:

instead of id 1 I am getting id 2. If I remove the group_concat it gives the correct output.

Advertisement

Answer

If you want the id of the row with the max value then use GROUP_CONCAT() for the ids ordered by value DESC and take the first one of the result with the function SUBSTRING_INDEX():

See the demo.
Results:

id firstName LastName mail value
1 John Seam sa@gmail.com|js@yahoo.com 450
3 Richard Min aa@gmail.com 0
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement