CODEWITHSUNDEEP
mysqlsql
sanazz
sanazz

Reputation: 35

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      [email protected]   450
2   John      Seam      [email protected]    0  
3   Richard   Min       [email protected]    0

expected output:

id  firstName LastName   email                          value
1   John      Seam      [email protected]|[email protected]       450
3   Richard   Min       [email protected]                     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      [email protected]|[email protected]       450
3   Richard   Min       [email protected]                     0

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

Upvotes: 0

Views: 117

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

This answers the original version of the question.

Your query is malformed -- the select is inconsistent with the group by. I would suggest a very different approach:

select t.*,
       (select group_concat(t2.email)
        from table t2
       ) as emails
from table t
order by value desc
limit 1;

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

Upvotes: 0

forpas
forpas

Reputation: 164214

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 [email protected]|[email protected] 450
3 Richard Min [email protected] 0

Upvotes: 2

nbk
nbk

Reputation: 49410

You can use something like this, if you have more than one row with 450, you will need to add also a rownumber to it

CREATE TABLE Table1
    (`id` int, `firstName` varchar(7), `LastName` varchar(4), `email` varchar(12), `value` int)
;
    
INSERT INTO Table1
    (`id`, `firstName`, `LastName`, `email`, `value`)
VALUES
    (1, 'John', 'Seam', '[email protected]', 450),
    (2, 'John', 'Seam', '[email protected]', 0),
    (3, 'Richard', 'Min', '[email protected]', 0)
;

SELECT 
t1.`id`, t1.`firstName`, t1.`LastName`,mailG,max_value
FROM Table1 t1 INNER JOIN
(SELECT `firstName`
, `LastName`
,GROUP_CONCAT(`email` SEPARATOR  ' | ') mailG
, MAX(`value`) max_value
FROM Table1 GROUP BY `firstName`, `LastName`) t2 
ON t1.`firstName` =  t2.`firstName` 
AND  t1.`LastName`  = t2.`LastName` 
AND  t1.`value`  = t2.max_value

id | firstName | LastName | mailG                       | max_value
-: | :-------- | :------- | :-------------------------- | --------:
 1 | John      | Seam     | [email protected] | [email protected] |       450
 3 | Richard   | Min      | [email protected]                |         0

db<>fiddle here

Upvotes: 0

Related Questions