SQL Group Select Query

Question

Hi, I have the following table:

 ----------- ---------- -----------
| AccountID | Password | IpAddress |
 ----------- ---------- -----------| 
|   1          1234      127.0.0.1 |
|   2          123       127.0.0.1 |
|   3          1234      127.0.0.1 |
|   4          12        127.0.0.2 |
|   5          123       127.0.0.2 |
|   6          12        127.0.0.2 |
|   7          1         127.0.0.2 |
|   8          123       127.0.0.3 |
|   9          123       127.0.0.3 |
 ----------- ---------- -----------

I want to select accountIDs, passwords and IpAddresses from it grouped by ipaddress where both passwords ipaddresses are the same and have more than one accountid. Rows where more than 1 accountids have the same password and ip. The result of this table would be rows 1,3(ip group 1); 4,6(ip group 2);8,9(group 3).

Thanks.

Answer 1

If I understood you correctly, this is what you want

select t1.* from(select password, IpAddress 
from YourTable
group by password, IpAddress
having count(*) > 1) t2
join YourTable t1 on t1.IpAddress = t2.IpAddress
and t1.password= t2.password