-3

i have table below field

Hour,PathId,Duration,Event,CellId,Channel

Here each cellid have four pathId(i.e, 0,1,2,3),Each pathId have many Events,Channel and Durations.

Now i want to display top 10 records(each pathId) for each cellid.

(group by cellid, pathid and channel we got duration.. we take top ten each pathid based on duration)

i have 50+ cellid and each cellid have four pathid(i.e, 0,1,2,3)

pls help me

! SampleTable

! outputtable

Shanmuga Vadivel
  • 11
  • 1
  • 5

1 Answers1

3

i want to display top 10 records(pathId) for each cellid.

You can use the ROW_NUMBER() function to do that, something like:

WITH Ranked
AS
(
   SELECT
     Hour,PathId,Duration,Event,CellId,Channel,
     ROW_NUMBER() OVER(PARTITION BY cellid ORDER BY pathId) AS RN
   FROM tablename
)
SELECT Hour,PathId,Duration,Event,CellId,Channel
FROM Ranked
WHERE RN <= 10

The function ROW_NUMBER() OVER(PARTITION BY cellid ORDER BY pathId) will generate a ranking number, by ordering the pathId for each group of cellid and then get the top 10. (Note that this will order by pathId ascending).

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/66127/discussion-on-answer-by-mahmoud-gamal-select-top-10-records-for-each-group-in-sq). – Taryn Dec 03 '14 at 14:12