For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

30 May 2011

Get Top N Rows For Each Group

Consider a table which store the Total Marks of each student in each standard
Create Table TblMarksheet
(
  StudentId varchar(40),
  Standard varchar(max),
  TotalMarks int
)
Insert Some Values into it
Insert into TblMarksheet
Select 1,1,10 union all
Select 2,1,50 union all
Select 3,1,5 union all
Select 4,1,30 union all
Select 5,2,0 union all
Select 6,2,9 union all
Select 7,2,8 union all
Select 8,2,10 union all
Select 9,3,1 union all
Select 10,3,3 union all
Select 11,3,6 union all
Select 12,3,2 union all
Select 13,4,10 union all
Select 15,4,9 union all
Select 15,4,8 union all

Now what we want is we want to find the Top 3 Students from Each Standard.
Query:-

With MyTempView
(
  StudentId varchar(40),
  Standard varchar(max),
  TotalMarks int,
  RowIndex int
)
as
(
  Select *,row_number () over (partition by Standard order by TotalMarks desc)
)

Select StudentId,Standard,TotalMarks From MyTEmpView where RowIndex<=3