Select Top Ten Each Group [duplicate]

Question

This my table and and my query

! MyTable

This is My query
select CellId, PathId, channel, sum(Duration) as min_duration 
from HourlySummary group by CellId, PathId, Channel  
order by cellid, pathid, sum(Duration) desc

In my table I have 50+ cellid and each cellid have four pathid(i.e, 0,1,2,3)

Now I want to display top 10 records (each pathId) for each cellid order by Duaration

Answer 1

It seems you want to apply the top 10 per cellID scenario, just not to the base table but to the aggregated results. The answer is really very similar. You can use your results as a table (because it is a virtual table) - just remove the order by clause - then apply @Mikael Eriksson's's solution and add the order by you want in the final output:

-- our results: a common table expression
; with YourTable as
  ( select CellId, PathId, Channel, sum(Duration) as SumDuration 
    from dbo.HourlySummary 
    group by CellId, PathId, Channel
  )
-- using the results and row numbering them to get top 10 per CellID
select T.CellID,
       T.PathID,
       T.Channel,
       T.SumDuration
from (
     select T.CellID,
            T.PathID,
            T.SumDuration,
            row_number() over(partition by T.CellID order by T.SumDuration desc) as rn
     from YourTable as T
     ) as T
where T.rn <= 10
order by CellID, PathID, SumDuration desc ;

---

You could also combine the two intermediate tables (the cte and the row numbering derived table) into one (cte or derived table), like this:

; with YourTable as
  ( select T.CellID,
           T.PathID,
           T.Channel,
           sum(T.Duration) as SumDuration,
           row_number() over(partition by T.CellID order by sum(T.Duration) desc) as rn
    from dbo.HourlySummary as T
    group by T.CellId, T.PathId, T.Channel
  )
select T.CellID,
       T.PathID,
       T.SumDuration,
       T.Channel,
from YourTable as T
where T.rn <= 10
order by CellID, PathID, SumDuration desc ;