# How to select top 10 records from each category [duplicate]

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

• You need to top 10 ordered by something right? Ordered by duration descending or perhaps duration ascending ? Dec 3 '14 at 7:34
• yes.I need top 10 record ordered by pathid or duration. Dec 3 '14 at 8:24

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;

• Mikael.. It's working fine.. But it take only 60 records.. I have 100+ cellid and 400+ pathid.. Each cellid have 4 pathid. Now my query is, If i select cellId 1 means, i should display top 10 each pathid for that cellid... Dec 3 '14 at 8:51
• You want 10 records from each category as per your title. What is a category? I assumed a category is the same as CellID. If category is something else, for instance CellID + PathID you just have to modify the partition by clause to include all columns that make a category partition by T.CellID, T.PathID. Dec 3 '14 at 8:55
• CellId is category.. Dec 3 '14 at 9:19
• @ShanmugaVadivel Well it isn't...CellID and PathID is your category based on your initial question, so as Mikael says, PARTITION BY T.CellID, T.PathID Dec 3 '14 at 9:24
• ok Mark.. I will try and let u know Dec 3 '14 at 9:29

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

• How to use group by inside Row_number() over (partition by ....order by)... It is possible? Dec 3 '14 at 13:48
• Could you compare your solution to the "partition by" one, please? What's better to use, when, why etc.? Jan 15 '16 at 15:01
• Nice one. I have tried more then 20 queries on 2.5 million records but your query is performing much fast. Jan 25 '17 at 11:15