MySQL - Using COUNT(*) in the WHERE Clause?

Ok so I want to find all the rows with the same value in. (Or at least a pair)

I.E.

James| 19.193.283.19
John| 20.134.232.344
Jack| 19.193.283.19 
Jonny| 19.193.283.19

I would want it to return rows James, Jack and Jonny -as more than one row has the IP '19.193.283.19' in it.

I tried doing what the other similar question answered:

select *
from `Zombie`
group by `Ip`
having count(*) > 1
order by `Ip` desc

But it just returned 1 row with a pair or more of the similar 'Ip' I want every row.

How would I modify the SQL so it returns all indisinct rows?

Thanks alot.

You could use an exists subquery to find all rows that have a matching row with the same Ip:

select  *
from    YourTable as yt1
where   exists
        (
        select  *
        from    YourTable as yt2
        where   yt1.name <> yt2.name
                and yt1.Ip = yt2.Ip
        )

Sorting by the number of rows with the same Ip can be done with a self-join, like:

select  yt1.name
,       yt1.Ip
from    YourTable as yt1
join    YourTable as yt2
on      yt1.name <> yt2.name
        and yt1.Ip = yt2.Ip
group by
        yt1.name
,       yt1.Ip
order by
        count(yt2.name) desc

Another way would be to join your table with the subquery you already have used (to find ips existing in more than one row):

SELECT t.name
     , t.Ip
FROM 
    YourTable AS t
  JOIN
      ( SELECT Ip
             , COUNT(*) AS cnt
        FROM YourTable
        GROUP BY Ip
        HAVING COUNT(*) > 1
      ) AS td
    ON td.Ip = t.Ip
ORDER BY
       td.cnt DESC
     , t.Ip
     , t.name