4

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

SampleTable

Sample Output

SampleOutput

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

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;
| improve this answer | |
  • 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... – Shanmuga Vadivel Dec 3 '14 at 8:51
  • 1
    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. – Mikael Eriksson Dec 3 '14 at 8:55
  • CellId is category.. – Shanmuga Vadivel 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 – Mark Sinkinson Dec 3 '14 at 9:24
  • ok Mark.. I will try and let u know – Shanmuga Vadivel Dec 3 '14 at 9:29
4

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
| improve this answer | |
  • How to use group by inside Row_number() over (partition by ....order by)... It is possible? – Shanmuga Vadivel 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.? – David Ferenczy Rogožan Jan 15 '16 at 15:01
  • 1
    Nice one. I have tried more then 20 queries on 2.5 million records but your query is performing much fast. – vicky Jan 25 '17 at 11:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.