Get list of ids for each in group in group by statement sqlite

Question

Let's suppose I have a sqlite table like the following:

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    field1 INTEGER,
    field2 INTEGER
);

INSERT INTO my_table (field1, field2) VALUES
    (1, 2),
    (2, 1),
    (2, 1),
    (3, 1),
    (1, 2),
    (2, 1);

Which looks like:

1|1|2
2|2|1
3|2|1
4|3|1
5|1|2
6|2|1

For each group of distinct values where the group size is larger than 1, I would like the set of corresponding ids. For the above this would be: 1,5 and 2,3,6.

If I run

SELECT id FROM my_table
GROUP BY
    field1, field2
HAVING count(*) > 1;

only the last for each group is returned, i.e. 5 and 6. Can I return all the ids for each group?

Answer 1

Without aggregation function you could get any value. SQLite is similiar to MySQL in that matter. In standard SQL you have to choose aggregation function, otherwise you will get error.

To get all values you could use GROUP_CONCAT:

SELECT field1, field2, GROUP_CONCAT(id, ',') AS ids
FROM my_table
GROUP BY field1, field2
HAVING COUNT(*) > 1;

SqlFiddleDemo

Output:

╔═════════╦═════════╦═══════╗
║ field1  ║ field2  ║  ids  ║
╠═════════╬═════════╬═══════╣
║      1  ║      2  ║ 1,5   ║
║      2  ║      1  ║ 2,3,6 ║
╚═════════╩═════════╩═══════╝

If you want ids only remove field1,field2 from select columns list.

SELECT GROUP_CONCAT(id, ',') AS ids
FROM my_table
GROUP BY field1, field2
HAVING COUNT(*) > 1

SqlFiddleDemo2