Update with result of GROUP BY

I kno开发者_开发技巧w enough SQL to write basic SELECT and UPDATEs. I'm struggling with JOINs

I want to update the 'county' field in a crime table. I am using a zip_xref table that will give me the county names. The issue is that several hundred cities are in multiple counties, and I want to update the table with the most prevalent county, so my sub-query must have a way to rank the counties by size.

I've written this to find the county names ranked by the number of zip codes in each county.

SELECT DISTINCT CountyName AS counties, COUNT(*) AS numZips  
FROM Zip_XRef WHERE (CityName = 'Aurora') AND (StateName = 'Colorado')  
GROUP BY CountyName ORDER BY numZips DESC

I know this UPDATE doesn't work, it's just the idea.

UPDATE crime_table_08 AS c  
SET county =   
    (SELECT TOP 1 DISTINCT CountyName, COUNT(*) AS numZips 
    FROM Zip_XRef AS z 
    WHERE RTRIM(z.StateName) = RTRIM(c.State) AND RTRIM(z.CityName) = RTRIM(c.city)
    GROUP BY CountyName ORDER BY numZips DESC
    )

I'm thinking that I'll need to create a temporary table, and then call it to update c, and I can't quite get my head around the logic. Any help greatly appreciated.

OR, Does anyone think there is a better way?

---

UPDATE c 
SET county =   t.CountyName
FROM crime_table_08 AS c  
CROSS APPLY (
   SELECT TOP (1) CountyName
   FROM (
       SELECT CountyName, COUNT(*) as cnt
       FROM Zip_XRef AS z 
       WHERE RTRIM(z.StateName) = RTRIM(c.State) AND RTRIM(z.CityName) = RTRIM(c.city)
       GROUP BY CountyName)
   ) AS s
   ORDER BY cnt DESC) as t;