Select * Recent * Class Information for each 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 Grad table. I am wondering if it is possible using SQL (Postgres) to select all students along with their latest class information. Basically, I want the result table to look like this: date, test_name and grade - for the last 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 SOLUTIONS

 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

Yes it is possible. The item you are looking for is " DISTINCT ON ". With it, you can easily execute a query without subqueries and multiple scans of the same table.

In the docs, pay attention to the ON part of "DISTINCT ON".

Answer 2

I think Postgre supports window functions, so you should 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: Obviously, windowing functions are not supported in the where clause (I should have known this, because it makes sense). This, however, is not an insoluble 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, however, if your data is large.

Answer 3

While I am not familiar with Postgres, I know a lot about working with Oracle. Perhaps the request below will 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

 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