Try this:
SELECT s.Stu_ID, s.Stu_Name, s.Class, MAX(s.Marks) AS 'Highest marks', s.Subject, d.Gender, d.Address
FROM STUDENT_CRpt AS s INNER JOIN STUDENT_DTL_Crpt AS d ON s.Stu_ID=d.Stu_ID and s.Class=d.Class
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject, d.Gender,d.Address
ORDER BY s.Class, s.Stu_Name
[EDIT #1]
Thank you for explanation ;)
To get higest marks in class for subject:
SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks',
FROM STUDENT_CRpt AS s
GROUP BY s.Class, s.Subject
To get highest marks for each student in class for subject:
SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
FROM STUDENT_CRpt AS s
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject
To get highest marks for each student for subject and for class:
SELECT t1.*, t2.[Highest marks] AS 'Highest in Class'
FROM (
SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
FROM STUDENT_CRpt AS s
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject
) AS t1 LEFT JOIN (
SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks',
FROM STUDENT_CRpt AS s
GROUP BY s.Class, s.Subject
) AS t2 ON t1.Class = t2.Class AND t1.Subject = t2.Subject
Note: Not tested, because i haven't access to SQL Server at this moment.
I hope it help.
[/EDIT]