# Select Top Ten Each Group [duplicate]

This my table and and my query

``````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`

• Duplicate, flagged. – Kris Gruttemeyer Dec 3 '14 at 15:09
• You already have a question about this where you accepted an answer. – ypercubeᵀᴹ Dec 3 '14 at 15:09

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 ;
``````