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:

id  firstName LastName  email         value
1   John      Seam      [e-mail]   450
2   John      Seam      [e-mail]    0  
3   Richard   Min       [e-mail]    0 

expected output:

id  firstName LastName   email                          value
1   John      Seam      [e-mail]|[e-mail]       450
3   Richard   Min       [e-mail]                     0 

I tried the following query:

select id,firstName,LastName,group_concat(email) ,max(value) 
from table 
group by firstName,LastName

but it gave wrong result:

id  firstName LastName   email                          value
2   John      Seam      [e-mail]|[e-mail]       450
3   Richard   Min       [e-mail]                     0 

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

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():

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY value DESC), ',', 1) id,
       firstName, LastName,
       GROUP_CONCAT(email SEPARATOR '|') email,
       MAX(value) value
FROM tablename
GROUP BY firstName, LastName

See the demo.
Results:

id	firstName	LastName	mail	value
1	John	Seam	[e-mail]|[e-mail]	450
3	Richard	Min	[e-mail]	0