On Thu, 2004-07-01 at 10:03, rmck wrote:
> Hi,
>
> I have a table with ip,port and I want to see the top ten Ip's with the most
> entries?
> Ip's can be in db many times...
>
> Not the first distinct 10... Im stuck...
>
> I have tried:
> mysql> select DISTINCT ip from iptable limit 10;
>
> +---------------+
> | ip |
> +---------------+
> | 0.0.0.0 |
> | 10.0.1.42 |
> | 10.0.1.8 |
> | 10.1.1.1 |
> | 10.10.10.1 |
> | 10.115.94.193 |
> | 10.115.94.195 |
> | 10.115.94.40 |
> | 10.122.1.1 |
> | 10.20.7.184 |
> +---------------+
> 10 rows in set (0.04 sec)
>
> mysql>
>
> But doesn't that just give the first 10 DISTINCT ip's??
Yes. You need to count the number of times an IP appears and sort by
that count, then limit it:
SELECT ip, COUNT(ip) as num
FROM iptable
GROUP BY ip
ORDER BY num DESC
LIMIT 10
--
. Garth Webb
. [EMAIL PROTECTED]
.
. shoes * éå * schoenen * ëí * chaussures * zapatos
. Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
