MySQL SELECT statement that groups results by id?

Question

Hi all,

I am trying to create a MySQL SELECT statement that will select a bunch of rows from a table and will group the results by the id of the row (multiple rows will have the same id).

Here's an example of what I'm trying to do. Let's say I have the following table:

id | name
1 | Art
1 | Arnold
1 | Anatoly
2 | Beatrice
2 | Bertha
2 | Betty
3 | Constantine
3 | Cramer

I'd like to have MySQL return the data grouped by id like so:

[1] => Art, Arnold, Anatoly [2] => Beatrice, Bertha, Betty [3] => Constantine, Cramer

I know I could do a simple SQL select, then loop over the result in PHP, but I'd like to let MySQL handle the grouping if possible. Any advice would be great. Thanks so much!

Rohan

Answer 1

One possible solution is to use MySQL's GROUP_CONCAT(expr) function.

SELECT
  GROUP_CONCAT(name)
FROM
  foo
GROUP BY
  id

But keep in mind:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.