How to select top 10 records from each category

Question

I have a table with fields

Hour,
PathId,
Duration,
Event,
CellId,
Channel

I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to display the top 10 records (each PathId) for each CellId.

Sample Table

Sample Output

Answer 1

Use row_number() in a derived table. Partition by CellID and use the order by as per your specification. In the main query you filter on rn to get the top 10 rows per category.

select T.CellID,
       T.PathID,
       T.Duration
from (
     select T.CellID,
            T.PathID,
            T.Duration,
            row_number() over(partition by T.CellID order by T.Duration desc) as rn
     from dbo.YourTable as T
     ) as T
where T.rn <= 10;

Answer 2

How about using CROSS APPLY eg

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp
(
    CellId      INT,
    PathId      INT,
    Duration    INT
)
GO

INSERT INTO #tmp VALUES
    ( 10, 1, 100 ),
    ( 10, 0, 120 ),
    ( 10, 2, 125 ),
    ( 10, 1, 111 ),
    ( 10, 0, 215 ),
    ( 10, 2, 22 ),
    ( 10, 0, 222 ),
    ( 10, 0, 55 ),
    ( 10, 1, 555 ),
    ( 10, 2, 58 ),
    ( 10, 0, 88 ),
    ( 10, 0, 9 ),
    ( 10, 2, 98 ),
    ( 10, 1, 55 ),
    ( 10, 2, 98 ),
    ( 10, 2, 74 ),
    ( 10, 0, 65 ),
    ( 10, 0, 66 ),
    ( 10, 1, 85 ),
    ( 10, 1, 37 ),
    ( 10, 1, 55 ),
    ( 11, 2, 11 ),
    ( 11, 0, 25 ),
    ( 11, 2, 69 ),
    ( 11, 0, 88 ),
    ( 11, 2, 54 ),
    ( 11, 0, 44 ),
    ( 11, 1, 22 ),
    ( 11, 0, 22 ),
    ( 11, 1, 55 ),
    ( 11, 1, 69 ),
    ( 11, 0, 55 ),
    ( 11, 1, 65 ),
    ( 11, 1, 65 ),
    ( 11, 0, 33 ),
    ( 11, 2, 35 )
GO


SELECT x.*
FROM ( SELECT DISTINCT cellId FROM #tmp ) c
    CROSS APPLY ( SELECT TOP 10 * FROM #tmp t WHERE c.cellId = t.cellId ) x