CODEWITHSUNDEEP
sql-server
Isaac Levin
Isaac Levin

Reputation: 2899

Get Top N for particular column value SQL Server

I have a table that I have collected of statistics, with 3 columns: id, year, and points. I would like to get the top N rows ordered by points per year. In the real scenario, there are more columns, but if I can get the ID and year, and get the rest using a subselect. Is there an easier way to do this rather than doing each year individually?

Upvotes: 0

Views: 69

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You can use the OVER clause with window functions to top N per group results

;WITH cte
     AS (SELECT Row_number()
                  OVER(
                    partition BY year
                    ORDER BY points Desc) Rn,
                *
         FROM   tablename)
SELECT *
FROM   cte
WHERE  rn <= 10

Upvotes: 2

Related Questions