By Shanmuga Vadivel


2014-12-03 07:13:20 8 Comments

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

2 comments

@wBob 2014-12-03 11:53:23

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

@Shanmuga Vadivel 2014-12-03 13:48:32

How to use group by inside Row_number() over (partition by ....order by)... It is possible?

@David Ferenczy Rogo┼żan 2016-01-15 15:01:25

Could you compare your solution to the "partition by" one, please? What's better to use, when, why etc.?

@vicky 2017-01-25 11:15:09

Nice one. I have tried more then 20 queries on 2.5 million records but your query is performing much fast.

@Mikael Eriksson 2014-12-03 08:30:58

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;

@Shanmuga Vadivel 2014-12-03 08:51:43

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

@Mikael Eriksson 2014-12-03 08:55:19

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.

@Shanmuga Vadivel 2014-12-03 09:19:34

CellId is category..

@Mark Sinkinson 2014-12-03 09:24:51

@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

@Shanmuga Vadivel 2014-12-03 09:29:25

ok Mark.. I will try and let u know

@Shanmuga Vadivel 2014-12-03 09:52:49

Mark.. do you have any idea Without using row_number(),PARTITION BY functions..

@Shanmuga Vadivel 2014-12-03 12:17:55

Mikael and Mark Thank you very much.. It is working fine.. Once again Thank You Very Much...

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] List top N rentals for each month

6 Answered Questions

[SOLVED] How to select the first row of each group?

2 Answered Questions

[SOLVED] How to select TOP 1 value from each column where value is not null

1 Answered Questions

[SOLVED] How can I get top two tags?

1 Answered Questions

2 Answered Questions

3 Answered Questions

[SOLVED] How to select distinct TOP but group by column

1 Answered Questions

Sponsored Content