SELECT a.block
, a.MYCOUNT
FROM
( SELECT b.*
, dr = DENSE_RANK() OVER ( PARTITION BY b.block, b.MYCOUNT ORDER BY b.MYCOUNT DESC )
FROM
( SELECT block
, count(block) AS MYCOUNT
FROM crimes
WHERE DESCRIPTION::text LIKE '%GUN%'
GROUP
BY block
) b
) a
WHERE a.dr = 1
I've added the Postgresql Topic Area.
Something along the dense_rank John posted should be what you are looking for. You just need to add district.
I was going to look at the data from the link you provided but canceled the download around 500M.
If you want tested SQL, please provide a subset of the data and the expectedresults from the data you provide.
Please post what database and programming language you are using.
Can you also post sample data in text for and expected results? That way we can set up a test case and provide tested SQL.