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

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;

CodePudding user response：

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.

CodePudding user response：

Looks as sub-query approach:

select email, group_concat(id order by id desc) as id 
from external_user 
where id not in (
  -- here filter out records with max id per email
  select max(id) as max_id from external_user group by email 
)
group by email;

SQL online environment