Select *Latest* Grade Information for every Student

Question

I have two tables:

STUDENT        GRADES
----------     ----------
id             id
name           person_id
address        date
city           test_name
phone          grade

Each Student will have several entries in the Grades table. I am wondering if it is possible using SQL (Postgres) to select all students along with their latest grade information. I basically want a result table that looks like the following, where date, test_name, and grade are for the latest result (by date).

LATEST_GRADES
----------------
id
name
address
city
phone
grade_id
date
test_name
grade

Any help would be greatly appreciated, thanks.

EDIT: ADDED SOLUTION QUERY

SELECT * FROM
  students s
  JOIN (SELECT DISTINCT ON (person_id) person_id, date, test_name, grade
      FROM grades
      ORDER BY person_id, date DESC) g
    ON s.id = g.person_id;

Answer 1

I think Postgre supports windowing functions, so you should be able to do something like

SELECT *
  FROM person p
  JOIN grades g ON grades.person_id = p.id
 WHERE row_number() OVER (PARTITION BY g.person_id ORDER BY g.date DESC) = 1

Edit: Apparently windowing functions are not supported in the where clause (should've known this since it makes sense). This is, however, not an unsolvable problem:

SELECT *
  FROM person p
  JOIN (SELECT person_id, <other_fields>, row_number() OVER (PARTITION BY person_id ORDER BY date DESC) AS rn FROM grades) g
 WHERE g.rn = 1

Check the execution plan, though, if your data is large.

Answer 2

select 
S.id, 
S.name, 
S.address, 
S.city, 
S.phone, 
G.id as grade_id, 
G.date,
G.test_name,
G.grade

from
   Grades G,
   Student S, 
   (select S.id as studentid, max(date) as latest_grade_date  
    from student S , grades G where s.id = g.person_id) Q
WHERE
   G.person_id = Q.studentid
   AND S.id = Q.studentid
   AND G.date = Q.latest_grade_date
   AND S.id = G.person_id

Answer 3

While I'm not familiar with Postgres, I do a lot of this kind of thing at work with Oracle. Perhaps the query below will be of help.

select p.id,
       p.name,
       p.address,
       p.city,
       p.phone,
       g.date,
       g.test_name,
       g.grade 
from person p, 
     grades g
where p.id = g.person_id 
  and g.date = (select max(g2.date) 
                     from grades g2
                     where g2.id = g.id
                )

Answer 4

Yes, it's possible. The clause you're looking for is "DISTINCT ON". With it you can easily do the query without subselects and multiple scans of the same table.

In docs, please notice the ON part of "DISTINCT ON".