In MySQL How do I SELECT all ids from rows with a similar value

Question

I want to pull all the unique IDs for particular rows with the same username and then display each result as a row.

For example...

Here's my table:

+----+------+
| id | name |
+----+------+
| 1  | Joe  |
| 2  | Amy  |
| 3  | Joe  |
| 4  | Amy  |
| 5  | Joe  |
| 6  | Amy  |
+----+------+

Here's the result I want:

+------+-------+
| name | ids   |
+------+-------+
| Joe  | 1,3,5 |
| Amy  | 2,4,6 |
+------+-------+

How do I pull this result in MySQL?

Might be able to do something like http://stackoverflow.com/questions/19558443/comma-separated-string-of-selected-values-in-mysql and include a way to do a group by name? — James Oravec, Mar 22 '16 at 21:23
Possible duplicate of [Group by one column and Show all results from another column](http://stackoverflow.com/questions/17716854/group-by-one-column-and-show-all-results-from-another-column) — Serge Seredenko, Mar 22 '16 at 21:29

score 6 · Accepted Answer · edited May 23 '17 at 12:31

6

Use a GROUP_CONCAT() with DISTINCT clause to aggregate unique ids for a particular name:

SELECT name, GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS ids
FROM yourtable
GROUP BY name

edited May 23 '17 at 12:31

Community

answered Mar 22 '16 at 21:23

Kamil Gosciminski

trincot · Answer 2 · 2016-03-22T21:33:20.007

You can use group_concat for that:

SELECT   name, GROUP_CONCAT(id) AS ids
FROM     table
GROUP BY name

You can also specify a separator, but the one by default is the comma.

You can also specify DISTINCT, but since id is unique, there is no reason to this: all it will do is slow down the query.

Here is SQL fiddle producing the output as desired:

+------+-------+
| name | ids   |
+------+-------+
| Joe  | 1,3,5 |
| Amy  | 2,4,6 |
+------+-------+

score 1 · Answer 3 · answered Mar 22 '16 at 21:24

1

This is a what the group_concat operator is designed for:

SELECT   name, GROUP_CONCAT(id) AS ids
FROM     mytable
GROUP BY name

answered Mar 22 '16 at 21:24

Mureinik

3 Answers3