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