Tasos Tasos - 7 months ago 28
SQL Question

Group By in MSSQL with the top 10 rows of each group

I have the following SQL query which I use to get some stats (AVG, MAX, MIN and COUNT) of each group in my database. The tables are two. Teams and Users. Each team has many users.

SELECT t.id, AVG(u.Age) AS Average, MIN(u.Age) AS Minimum, MAX(u.Age) AS Maximum, COUNT(u.id) AS NumberOfUsers

FROM Teams t

INNER JOIN Users u ON t.id = u.id

WHERE t.status IS NOT NULL

GROUP BY t.id

ORDER BY Average Desc


However, the table Users has another column "Experience" and I want to get the same stats for the top 10 users in each group based on that column.

Any idea?

Answer

The solution is to use row_number(), in this case, I think in a subquery:

SELECT t.id, AVG(u.Age) AS Average, MIN(u.Age) AS Minimum,
       MAX(u.Age) AS Maximum, COUNT(u.id) AS NumberOfUsers
FROM Teams t INNER JOIN
     (SELECT u.*,
             ROW_NUMBER() OVER (PARTITION BY id ORDER BY experience DESC) as seqnum
      FROM Users u
     ) u
     ON t.id = u.id  AND -- is this really the right join condition?
        seqnum <= 10
WHERE t.status IS NOT NULL
GROUP BY t.id
ORDER BY Average Desc;

I suspect that your join conditions are wrong. I would expect the proper join condition to use something like u.TeamId instead of u.Id. If so, you need to change the partition by column to be the same as the join column.

Comments