MySQL has useful extention to the GROUP BY operation – function GROUP_CONCAT:
GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group.
Returns NULL when there are no non-NULL values.
Where it can be useful?
For example to get array without looping inside the code, In single SQL query we can get it.
[sql]
CREATE TABLE services (
id INT UNSIGNED NOT NULL,
client_id INT UNSIGNED NOT NULL,
KEY (id));
INSERT INTO services
VALUES (1,1),(1,2),(3,5),(3,6),(3,7);
SELECT id,client_id FROM services WHERE id = 3;
+—-+———–+
| id | client_id |
+—-+———–+
| 3 | 5 |
| 3 | 6 |
| 3 | 7 |
+—-+———–+
SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;
+—-+————————-+
| id | GROUP_CONCAT(client_id) |
+—-+————————-+
| 3 | 5,6,7 |
+—-+————————-+
SELECT id,GROUP_CONCAT(DISTINCT client_id ORDER BY client_id DESC SEPARATOR ‘ ‘) FROM services GROUP BY id;
+—-+————————————————————————+
| id | GROUP_CONCAT(DISTINCT client_id ORDER BY client_id DESC SEPARATOR ‘ ‘) |
+—-+————————————————————————+
| 1 | 2 1 |
| 3 | 7 6 5 |
+—-+————————————————————————+
[/sql]
The above MySQL statement will return unique client_id’s, as a list of strings separated by the specified separator ‘ ‘(space) in descending order for each group of ‘id’ from the services table. The order can be changed in ascending, using ‘ASC’ option instead of ‘DESC’ at the end of the select statement.