SQLTeam.com | Weblogs | Forums

How to get top 50 per client instead of top 50 overall

The query below brings back 50 records. If there are 10 clients in the database, I want it to bring back 500 records (the top 50 for each client). How do I modify this query to do that?

select top 50 count(), clients.id, clients.accountid, courses.Name
from courses
inner join clients on clients.id = courses.clientid
inner join courseenrollments on courses.id = CourseEnrollments.CourseId
where CourseEnrollments.DateCompleted between '2019-01-01' and '2020-01-01'
group by clients.id, clients.AccountId, courses.Name
order by count(
) desc, clients.Id

select derived.*
from clients cl
cross apply (
    select top (50) count(*) as clientcount, co.clientid, cl.accountid, co.Name
    from courses co
    inner join courseenrollments ce on co.id = ce.CourseId
    where co.clientid = cl.id and 
        ce.DateCompleted between '20190101' and '20200101'
    group by co.clientid, cl.AccountId, co.Name
    order by count(*) desc, co.clientid
) as derived
order by clientid, clientcount
1 Like