/ p>

The Following should work

SELECT TOP 1 S.STREET_NAME,COUNT(a.*) AS dangerous_street 
FROM ACCIDENTS_STREETS A 
inner Join STREET S on S.ID = A.STREET_ID 
GROUP BY S.STREET_NAME ORDER BY dangerous_street DESC