steeped steeped - 1 year ago 60
SQL Question

MySQL: Sort most popular names with query, but specific to each IP

I have a table set up like this:

id | ip | name
---------------------------------
1 | 54.34.32.222 | John
2 | 23.44.64.843 | Rick
3 | 54.34.32.222 | John
4 | 23.44.64.843 | John
5 | 14.432.45.45 | Lisa
6 | 54.34.32.222 | Lisa
7 | 14.432.45.45 | Lisa


I want to query and sort the most popular names. I do not want to include any more than one IP per name.

For example, "54.34.32.222" appears for John twice, so I only want to grab the first row. But "54.34.32.222" also appears for Lisa, so I would like to grab that IP as well.

The result should look something like this:

id | ip | name
---------------------------------
1 | 54.34.32.222 | John
2 | 23.44.64.843 | Rick
4 | 23.44.64.843 | John
5 | 14.432.45.45 | Lisa
6 | 54.34.32.222 | Lisa


Lisa and John would then be the most popular names, and should appear at the top of the sort, followed by Rick.

I would like my final query to return something that looks like:

name | count |
--------------------
Lisa | 2 |
John | 2 |
Rick | 1 |


Is it possible to do this in a single query? Or even two? Your help is much appreciated!

vkp vkp
Answer Source

You can order by the count of distinct ip's per name. In case of a tie with the counts, you can order by name or some other column.

select name, count(distinct ip) as cnt
from tablename
group by name
order by cnt desc, name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download