Different Max Levels, Same Table

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

select *
from
(
    select *, rn = row_number() over (partition by PupilID, SubjectID, AssesementID order by Level desc)
    from #tblexams
) d
where rn = 1
1 Like

@khtan

I'm speechless at the speed and the effectiveness of your syntax - thank you!
Is there anyway in which I can structure the query so that results from assessment 2 would be in one column and results from assessment 15 would be in an adjacent column?

; with cte as
(
    select *
    from
    (
        select *, rn = row_number() over (partition by PupilID, SubjectID, AssessmentID order by Level desc)
        from #tblexams
    ) d
--     where rn = 1
)
select    PupilID, SubjectID, [2] as [Assessment 2 Result], [15] as [Assessment 15 Result]
from    cte
    pivot
    (
        max(Level)
        for AssessmentID in ([2], [15])
    ) p
1 Like

@khtan

Fantastic, thank you very much