0

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.

wannabesenior
  • 46
  • 4
  • Your syntaxe is wrong : – wannabesenior Oct 27 '21 at 09:42
  • GROUP_CONCAT(a.artiste_nom ORDER BY column SEPARATOR '|' ) but it didnt work – wannabesenior Oct 27 '21 at 09:43
  • *but it didnt work* The values ordering obtained after ORDER BY is defined by used collation. If it seems to you that the sorting does not match specified one then you cannot see something. For example, there is some symbol which looks like another one visually but have less priority in current collation. You may check what you see really using, for example, HEX(). Try to reproduce your problem on some online fiddle. – Akina Oct 27 '21 at 10:25

2 Answers2

2

You can specify the order in GROUP_CONCAT with ORDER BY parameter.

Try using:

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

Or with DISTINCT to ensure duplicated values are concatenated only one time

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

See: Maria DB Group Concat Document

ThangLeQuoc
  • 2,272
  • 2
  • 19
  • 30
1

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)

wannabesenior
  • 46
  • 4