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

UPDATE crime_table_08
SET County = (
	SELECT TOP 1 county
	FROM 
		(CREATE TABLE #countyTemp (
			county VARCHAR(255),
			numZips int IDENTITY (1, 1) 
			)
		INSERT INTO #countyTemp (county, numZips)
			SELECT CountyName, COUNT(*) 
       				FROM Zip_XRef AS z, crime_table_08 AS c
       				WHERE RTRIM(z.StateName) = RTRIM(c.State) AND RTRIM(z.CityName) = RTRIM(c.city)
       				GROUP BY CountyName
		)
	)
DROP TABLE #countyTemp

update ct set
       ct.county = ca.countyname
from   crime_table_08 ct
cross apply (
       select top 1 countyname
       from   zip_xref
       where  rtrim(statename) = rtrim(ct.state)
       and    rtrim(CityName) = rtrim(ct.city)
       group by countyname
       order by count(*) desc
       ) ca

--not needed unless you want to limit the update to just this city/state
where  ct.CityName = 'Aurora'
and    ct.StateName = 'Colorado'

SELECT Z.statename, Z.CityName, Z.CountryName, COUNT(*) AS NumZips
INTO #t
FROM zip_xref Z
GROUP BY Z.statename, Z.CityName, Z.CountryName

UPDATE C
SET county = D.CountryName
FROM crime_table_08 C
	JOIN
	(
		SELECT T.statename, T.CityName, MIN(T.CountryName) AS CountryName
		FROM #t T
			JOIN
			(
				SELECT T1.statename, T1.CityName, MAX(NumZips) AS NumZips
				FROM #t T1
				GROUP BY T1.statename, T1.CityName
			) D1
				ON T.statename = D1.statename
					AND T.CityName = D1.CityName
					AND T.NumZips = D1.NumZips
		GROUP BY T.statename, T.CityName
	) D
		ON C.state = D.statename
			AND C.city = D.CityName

DROP TABLE #t