3

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.

ekad
  • 14,436
  • 26
  • 44
  • 46
Rohan
  • 383
  • 3
  • 6
  • 14

1 Answers1

4

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.
VolkerK
  • 95,432
  • 20
  • 163
  • 226
  • @MikeB, it haunts me everyday: I was born on 10/24. :P – Alix Axel Apr 27 '10 at 00:16
  • thanks for the reply @VolkerK, but I will definitely run into the max length issue since I'm dealing with thousands of rows =) – Rohan Apr 27 '10 at 01:30
  • 1
    @user why do you need such amount of data? what you gonna do with a line containing thousand of comma-separated names? – Your Common Sense Apr 27 '10 at 04:31
  • 3
    @user259878: the variable can be changed in the current session by `SET @@group_concat_max_len:= 1024*100;`, up to 4294967295. But the question of Col. Shrapnel is still relevant. – newtover Apr 27 '10 at 08:23