Need help with a specific query

Hey guys, I've tried playing around with something, and I can't find the answer.

I need a query to help me keep my site clean. It needs to look something like this:

SELECT * FROM 'table' WHERE 'id' is the same AND number of records > 3

So let's say my table looks like this:

ID NAME
1 John
1 James
1 Alex
2 Kira
2 Mike
2 Nancy
2 Elenora
3 Raymond
3 Taylor

--------------

Only ID 2 should be returned because it is the only ID with more then 3 records. I would just loop through with a mysqli_num_rows, but with the size of the table it takes way too long and it not a healthy way to do it.

Is it even possible to do this? I've tried google, but I can't find anything. I would appreciate any help. :)

Trivial.


SELECT id, COUNT(*) AS howmany
FROM table
GROUP BY id
HAVING howmany > 3

If you want all the info (not just the ID's) then you have to join back to the original table, again:

SELECT T.* FROM table AS T,
( SELECT id, COUNT(*) AS howmany
FROM table
GROUP BY id
HAVING howmany > 3 ) AS SUBS
WHERE T.id = SUBS.id
ORDER BY T.id, T.name


Finally, if you want to DELETE all the records where the count is 3 or less, you do similar:

DELETE FROM table
WHERE id IN (
SELECT id
FROM table
GROUP BY id
HAVING COUNT(id) <= 3 )

Old Pedant, you are a lifesaver! I don't know how to thank you. :D But thank you so much. :)