#1
  1. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144

    Group_concat ordering


    Hi--

    I'm wondering if it's possible to order the results of a group_concat. Here's my query:

    Code:
    select food.item_name, food.instructions, GROUP_CONCAT(ingredients.qty,'~',ingredients.unit,'~',ingredients.ingredient SEPARATOR '|') AS ingredient FROM food LEFT OUTER JOIN ingredients ON ingredients.fkey=food.pkey WHERE food.rkey=82 GROUP BY food.item_name ORDER BY ingredients.fkey ASC
    This pulls everything I need but the ingredients are pulled in some weird order. I'd like them in a specific order like this:

    Code:
    select food.item_name, food.instructions, GROUP_CONCAT(ingredients.qty,'~',ingredients.unit,'~',ingredients.ingredient, ingredients.fkey SEPARATOR '|' ORDER BY ingredients.fkey) AS ingredient FROM food LEFT OUTER JOIN ingredients ON ingredients.fkey=food.pkey WHERE food.rkey=82 GROUP BY food.item_name ORDER BY ingredients.fkey ASC
    Thanks!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    381
    Yes. See the manual.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    71
    Rep Power
    23
    That was helpful.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    381
    Glad to help.
  8. #5
  9. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,142
    Rep Power
    1320
    the ingredients are pulled in some weird order
    Would you care to enlighten us? Might be the first necessary step in getting help with your problem.
  10. #6
  11. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144
    The ingredients were pulled in a random order.

    I did in fact check the manual prior to posting but sometimes the manual is a little cryptic and without good examples. So with some trial and error I did find the answer:

    Code:
    select food.item_name, food.instructions, GROUP_CONCAT(ingredients.qty,'~',ingredients.unit,'~',ingredients.ingredient, ingredients.fkey ORDER BY ingredients.fkey SEPARATOR '|') AS ingredient FROM food LEFT OUTER JOIN ingredients ON ingredients.fkey=food.pkey WHERE food.rkey=82 GROUP BY food.item_name ORDER BY ingredients.fkey ASC
    Just put the ORDER BY before the SEPARATOR and all is well.

    Comments on this post

    • cafelatte disagrees : The manual IS sometimes a little cryptic. On this particlar point however, with the examples provided, it is entirely unambiguous.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,743
    Rep Power
    4288
    Originally Posted by Frank Grimes
    I did in fact check the manual prior to posting but sometimes the manual is a little cryptic and without good examples.
    i've been thinking for a while of having a tutorial/article web site called ETFM
    Are you tired of having people tell you to RTFM? Did you actually read the fine manual but you don't get it? You need
    ETFM -- Explain The Fine Manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    71
    Rep Power
    23
    Let us know when you build it... I would happily donate a few bucks.
  16. #9
  17. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144
    Stupid as it sounds, it would actually be very helpful.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo