SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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'])

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    Code:
    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


    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jip View Post
    Here's what I have so far:
    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...

    Quote Originally Posted by Jip View Post
    This is actually sorting as hoped. Do i still require a GROUP BY clause?
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •