Is there possible to remove 1 records or max(id) from GROUP_CONCAT MYSQL

June 13, 2022

I have to remove/skip the 1st records or max id in group_concat MYSQL.
Here is query

select email, group_concat(id order by id desc) as id 
from api_admin.external_user 
group by email 
having count(1) > 1;

>Solution :

Your GROUP_CONCAT() returns a string which is a comma separated list of at least 2 ids (because of the condition in the HAVING clause) sorted descending.
You can use the function SUBSTRING_INDEX() to get the part of the returned string after the first comma:

SELECT email, 
       SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', -COUNT(*) + 1) AS ids 
FROM external_user 
GROUP BY email 
HAVING COUNT(*) > 1;

See the demo.