Hi. I'm trying to get (from the same table) the maximum level attained for individual pupils in different subjects taken in different assessments. So for example, a pupil may have taken English several times in "assessment 2" and several times in "assessment 15" - I need to extract the maximum level attained in "assessment 2" and "assessment 15"
My table looks like this:
CREATE TABLE #tblexams
(
PupilID int
NOT NULL
,SubjectID int
NOT NULL
,Level int
NOT NULL
,AssessmentID int
NOT NULL
);
INSERT INTO #tblexams
VALUES (3110, 2, 145, 2
)
,(3110, 2, 210, 2
)
,(3110, 2, 170, 15
)
,(3110, 2, 181,15
);
And I'm trying to get the result to look like this:
CREATE QUERY #qryresults
(
PupilID int
NOT NULL
,SubjectID int
NOT NULL
,Assessment 2 Result int
NOT NULL
,Assessment 15 Result int
NOT NULL
);
INSERT INTO #qryresults
VALUES (3110, 2, 210, 2
)
,(3110, 2, 181,15
);
I have tried using subqueries with GROUP BY and MAX, but I can't seem to get the syntax right
Using Microsoft SSMS 2014
Thanks in advance