vshiva2379 (7/27/2013)
Hi all,suppose if we have student name, and subjects so we want student name and max marks scored subject for each student
one student will be scored in maths, another one scored in science like that
so i want student name, maths
student name, science
Thank you
Welcome aboard! For future posts, please see the article at the first link in my signature line below.
Since you're new...
--===== WITHOUT Ties
WITH
cteEnumerateScores AS
(
SELECT StudentName, SubjectName, Score,
ScoreOrder = RANK() OVER (PARTITION BY StudentName ORDER BY Score DESC)
FROM dbo.YourTable
)
SELECT StudentName, SubjectName, Score
FROM cteEnummerateScores
WHERE ScoreOrder = 1
ORDER BY StudentName
;
Of course, since I don't know the actual names of your columns or your table, you'll need to make some changes.
--Jeff Moden
Change is inevitable... Change for the better is not.