Sql Query t find out maximum from name field

I have 5 tables with field name name 开发者_如何学编程. & which name is appear maximum time in each table then I need to find out which output is maximum in all answer

select top 1 name, count(*) from table
group by name
order by count(*) desc

Just to add to the other response, if there is more than one name with the top number of occurrences then something along these lines will work:

;WITH cte (Name, NoOccurrences) AS 
(
  SELECT Name, COUNT(Name) FROM [Table] GROUP BY Name
)
SELECT Name, NoOccurrences FROM cte 
WHERE NoOccurrences = (SELECT MAX(NoOccurrences) FROM cte)