GROUP_CONCAT(DISTINCT xxx) reverse the order of the concatened values-CodePudding

I have some trouble with my sql request, when i compare thoses two GROUP_CONTACT, results seems to be reverse. I tried to put an order statement into the group_concat but with no success.

How can i get the same result between GROUP_CONCAT without DISTINCT statement and GROUP_CONCAT with DISTINCT statement.

SELECT GROUP_CONCAT(a.artiste_nom SEPARATOR '|') as 'std', GROUP_CONCAT(DISTINCT a.artiste_nom SEPARATOR '|') as 'std1'
FROM produit p
LEFT JOIN produit_artiste pa ON pa.produit_id = p.produit_id 
LEFT JOIN artiste a ON pa.artiste_id = a.artiste_id
WHERE pa.produit_id = p.produit_id
GROUP BY p.produit_id 
HAVING std <> std1

while using GROUP_CONCAT(DISTINCT a.artiste_nom ORDER BY a.artiste_nom ASC SEPARATOR '|') made no difference at all

GROUP_CONCAT(DISTINCT a.artiste_nom ORDER BY a.artiste_nom DESC SEPARATOR '|') had changed the way the table is parse.

CodePudding user response：

Try using

GROUP_CONCAT(a.artiste_nom ORDER BY a.artiste_nom ASC SEPARATOR '|')

You can specify the order in GROUP_CONCAT with ORDER BY parameter.
See: Maria DB Group Concat Document

CodePudding user response：

GROUP_CONCAT(DISTINCT(a.artiste_nom) ORDER BY a.artiste_id ASC SEPARATOR '|') did the job. (It seems like DISTINCT reverse the order of ids)