android sqlite query with same ID

Question

below is my sqlite table. I want to select those column with same ID numbers.

id object

"0" "person"

"1" "person"

"2" "person"

"3" "person"

"4" "chair"

"5" "chair"

"5" "person"

"6" "chair"

"6" "person"

"7" "person"

"8" "chair"

"8" "person"

"9" "chair"

For example, below same row Id has to be queried from the above table.

"5"
"6"
"8"

please help me to prepare a sqlite android query to select those same "id".

thanks in adv Senthil

Answer 1

Use grouping, and count how many rows are in the group:

SELECT id
FROM MyTable
GROUP BY id
HAVING COUNT(*) > 1;

Answer 2

This is one way to do it, maybe not the more performative one, though.

SELECT o1.*, (SELECT COUNT(*) FROM objects o2 WHERE o2.id = o1.id LIMIT 1) as count
FROM objects o1
WHERE count > 1;