mercredi 2 mars 2016

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

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?

Aucun commentaire:

Enregistrer un commentaire