CODEWITHSUNDEEP
sqlsql-servergroup-by
user1711657
user1711657

Reputation: 83

SQL to group on maximum of two columns

I am having trouble displaying data from two tables, using what I think should be a group method. I currently have a table containing pupils, and another containing the grades achieved (points) each year and term. See below:

PupilID, FirstName, Surname, DOB

GradeID, PupilID, SchoolYear, Term, Points

I want to query both tables and display all pupils with their latest grade, this should look for the maximum SchoolYear, then the maximum Term, and display the Points alongside the PupilID, FirstName and Surname.

I would appreciate any help anyone can offer with this

Upvotes: 0

Views: 39

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

This will select the latest grade per pupil based on SchoolYear and Term

select * from (
    select p.*, g.schoolyear, g.term,
        row_number() over (partition by PupilID order by SchoolYear desc, Term desc) rn
    from pupils p
    join grades g on g.PupilID = p.PupilID
) t1 where rn = 1

Upvotes: 1

dba2015
dba2015

Reputation: 127

try this

declare varSchoolYear int
declare vartrem int
set varSchoolYear=(select max (SchoolYear) from Grade)
set vartrem=(select max(term) from Pupil where SchoolYear=varSchoolYear)

select a.firstname,b.idgrade
from pupil a
inner join grade b
on a.pupilid = b.pupilid
where b.term=vartrem and b.SchoolYear=varSchoolYear

Upvotes: 0

Related Questions