Maximum value selection

Question

I have a database table with a phone number (row) and a name (row)

The same number may have different names on different lines.

given a certain number, I want to choose the most commonly used name

For example, given the following data when using 555.1234, I should return from Frank's request

Number   |  Name
-----------------
555.1234 |  Frank
555.1234 |  Fran
555.1234 |  James
555.1234 |  Frank
555.1233 |  Jesse

It seems that I should somehow combine different and Max, but I can not come up with the right query to do this. Any ideas?

Answer 1

, , , . , . (*) ( ) , .

select Top 1 Name, Count(*) total 
   from phone where number = '555.1234' 
   group by name 
   order by total desc

Answer 2

, , :

SELECT Number, Name, COUNT(Name) AS NameCount
FROM test
GROUP BY Number, Name
HAVING NameCount > 1
Order BY NameCount DESC
LIMIT 1

, .

test, :

Number   | Name
555.1234 | Frank
555.1234 | Fran
555.1234 | James
555.1234 | Frank
555.1234 | Jesse
555.2234 | Frank
555.1234 | Jesse
555.1234 | Frank

, !