Here's the modified query to find the Maximum and Second Maximum value in a Group/Category using a CTE in SQL Server 2005/2008
Sample Data
DECLARE @Student TABLE
(
StudentId int, SubjectId int, Marks float
)
INSERT @Student
SELECT 1,1,8.0 UNION ALL
SELECT 2,1,5.0 UNION ALL
SELECT 3,1,7.0 UNION ALL
SELECT 4,1,9.5 UNION ALL
SELECT 1,2,9.0 UNION ALL
SELECT 2,2,7.0 UNION ALL
SELECT 3,2,4.0 UNION ALL
SELECT 4,2,7.5
Query to fetch the highest and second highest marks in each Subject
;With CTE
AS
(Select Row_number() Over(Partition By SubjectId Order By Marks Desc) as Topp,* From @Student)
Select SubjectId,
Max(Case When Topp=1 Then Marks End) as '1st Rank',
Max(Case When Topp=2 Then Marks End) as '2nd Rank'
From CTE
Group By SubjectId
Results
SubjectId 1st Rank 2nd Rank
1 9.5 8
2 9 7.5
No comments:
Post a Comment