Reputation: 21749

SELECTING repetitive rows

I have saved all users' IPs in MYSQL. How can I SELECT users who registered with the same IP twice or more?

I know DISTINCT function in MySQL is for selecting unique rows, what about my situation? Are the any functions or I have just to run loop?

Thank you.

Upvotes: 1

Answers (3)

Reputation: 1224

SELECT count(*) cnt, name
FROM `users`
GROUP BY ip
HAVING cnt >1

Upvotes: 3

Reputation: 12704

SELECT userid, ip
FROM users
GROUP BY userid, ip
HAVING COUNT(userid) > 1;

Upvotes: 1

Reputation: 2923

SELECT ip FROM table GROUP BY ip HAVING COUNT(users)>1

Upvotes: 1