How to get 3 most frequent column counts separated by year in SQL-CodePudding

I have a database (crimes) and I want to separate per year the top 3 districts with the most frequent amount of crimes in SQL. I have tried the following code but this just sums the amount of crimes:

SELECT 
  year,
  district,
  CrimeID,
  COUNT(*) OVER (PARTITION BY year)
FROM Crimes

CodePudding user response：

You could do it like this in Oracle, if that helps (editing to add, it looks like you might be using SQL Server so I have added an alias to the derived table to make it work for that too):

SELECT
v.year,
v.district,
v.count

FROM (
    SELECT
    year,
    district,
    COUNT(*) AS count,
    ROW_NUMBER() OVER (PARTITION BY year ORDER BY COUNT(*) DESC) AS rono

    FROM crimes

    GROUP BY year, district
) v

WHERE v.rono <= 3
ORDER BY v.year ASC, v.rono ASC