How to add ORDER BY With GROUP_CONCAT?

Wanting to add ORDER BY to sort items grouped as a result of the GROUP_CONCAT. Any recommendations?

SELECT t1.id, t1.filename, t2.process, t3.folder_name, t3.userid AS folder_userid, t1.inout, t1.userid, t1.last_updated, t1.filesize, t1.pagecount, GROUP_CONCAT(t4.comment SEPARATOR ’ | ') AS concat_comments FROM (proposal_files AS t1 JOIN prop_file_processes AS t2 ON t1.process = t2.id) JOIN folders AS t3 ON t1.folder = t3.id LEFT JOIN prop_file_version_comments AS t4 ON t1.id = t4.fileid WHERE t1.propid = " . sql_quote($_SESSION[‘current_prop_id’])

i’ve decided that any day now i am just not even going to bother reading threads where the original poster does not care about the human beings who might want to read the query, but gives us the monolithic machine version instead

okay, just kidding, i will not only read it, i’ll format it for you

SELECT t1.id
     , t1.filename
     , t2.process
     , t3.folder_name
     , t3.userid AS folder_userid
     , t1.inout
     , t1.userid
     , t1.last_updated
     , t1.filesize
     , t1.pagecount
     , GROUP_CONCAT(t4.comment SEPARATOR ' | ') AS concat_comments 
  FROM proposal_files AS t1 
INNER
  JOIN prop_file_processes AS t2 
    ON t2.id = t1.process   
INNER
  JOIN folders AS t3 
    ON t3.id = t1.folder
LEFT OUTER
  JOIN prop_file_version_comments AS t4 
    ON t4.fileid = t1.id
 WHERE t1.propid = " . sql_quote($_SESSION['current_prop_id'])

what column did you want to sort the comments by?

by the way, y’all forgot your GROUP BY clause

:slight_smile:

Wow, thx for the quick feedback and the human-friendly formatting! I need to sort by the t4.timestamp field. Here’s what I have so far:

SELECT t1.id,
t1.filename,
t2.process,
t3.folder_name,
t3.userid AS folder_userid,
t1.inout,
t1.userid,
t1.last_updated,
t1.filesize,
t1.pagecount,
GROUP_CONCAT(t4.comment ORDER BY t4.timestamp DESC SEPARATOR ’ | ') AS concat_comments
FROM (
proposal_files AS t1
JOIN prop_file_processes AS t2
ON t1.process = t2.id
)
JOIN folders AS t3 ON t1.folder = t3.id
LEFT JOIN prop_file_version_comments AS t4 ON t1.id = t4.fileid
WHERE t1.propid = ‘" . sql_quote($_SESSION[‘current_prop_id’]) . "’";

This is actually sorting as hoped. Do i still require a GROUP BY clause?

amazing… i reformat it for you, but instead of taking my version, you reformat it yourself, leaving in those evil backticks that i carefully removed, and ignoring my other changes…

awesome that it does

i would say that you do still need a GROUP BY

otherwise, how would it know you wanted to concatenate the comments for each file? maybe you wanted comments for each folder? or comments for each user?