List users with the same IP
I have this query,
SELECT * FROM users
WHERE user_ip IN (SELECT user_ip FROM users GROUP BY user_ip having count(*) > 1)
ORDER BY user_ip
This works to list all users which has at least 1 repeated IP with another user.
I need to order all users by total of repeated IP.
ex. this users table
id, username, ip
1, user1, 1.1.1.1
2, user2, 2.2.2.2
3, user3, 1.1.1.1
4, user4, 4.4.4.4
5, user5, 2.2.2.2
6, user6, 2.2.2.2
should print,
ip, username, total
2.2.2.2, user2, 3
2.2.2.2, user5, 3
2.2.2.2, user6, 3
1.1.1.1, user1, 2
1.1.1.1, user3, 2
4.4.4.4, user4, 1
2Answer
Here is an approach which uses an INNER JOIN:
SELECT u1.ip, u1.username, u2.total
FROM users u1
INNER JOIN
(
SELECT ip, COUNT(*) AS total
FROM users
GROUP BY ip
) u2
ON u1.ip = u2.ip
ORDER BY u2.total DESC
Click the link below for a running demo:
- answered 8 years ago
- Sandy Hook
SELECT ip, username, count(*) total FROM user_ip WHERE ip in ( SELECT ip FROM user_ip GROUP BY 1 HAVING count(*) > 1 ) GROUP BY 1,2 ORDER BY 3 DESC,1,2
- answered 8 years ago
- Sunny Solu
Your Answer