steeped steeped - 6 months ago 7
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

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
Comments