SQLTeam.com | Weblogs | Forums

Different Max Levels, Same Table


#1

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


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

#3

@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?


#4
; 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

#5

@khtan

Fantastic, thank you very much