0

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

user1711657
  • 83
  • 1
  • 7
  • This is known as a [tag:greatest-n-per-group] problem. [There are many duplicates](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category). – Clockwork-Muse Mar 29 '15 at 22:26

2 Answers2

1

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
WorkSmarter
  • 3,738
  • 3
  • 29
  • 34
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • I get the following error when trying this Incorrect syntax near the keyword 'where'. – user1711657 Mar 29 '15 at 21:59
  • 1
    @user1711657 forgot the `t1` alias. try again – FuzzyTree Mar 29 '15 at 22:15
  • 2
    To avoid ambiguous column name error, update the inner select statement with something like this select p.*, g.schoolyear, g.term, Example of working code on SQL Fiddle http://sqlfiddle.com/#!6/7b0db/2/0 – WorkSmarter Mar 29 '15 at 22:20
  • Seems to be working now, thanks. exactly what I need. Glad I asked now i'd never have been able to figure this out on my own – user1711657 Mar 29 '15 at 22:28
  • Sorry, missed something. I need to show the recent grade for each subject. The grades table also contains a column named GradeSubject – user1711657 Mar 29 '15 at 22:33
  • I've managed to do this by adding the subject to the partition by query. thanks again – user1711657 Mar 29 '15 at 22:54
0

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
dba2015
  • 127
  • 1
  • 4
  • 13
  • Not quite - this gets the grades for the most recent year/term, but not the students' latest grades (can you spot the difference?). – Clockwork-Muse Mar 29 '15 at 22:28