>I have an assignment table (master table) and a review table (detail
table). The review table holds one or more records for each record in the
>assignment table. I need a query to return the latest review for each
record in the assignment table. Could someone advise me on a query to
do this.
>The assignment table has the following fields: assignid, assign_date,
manager, Projectid
>The review table has the following fields: reviewid, assignid,
review_date, progress_note
>Sorry just to add that I want the query to return projectid,
assign_date, review_date and Progress_note
Simple, and thanks for a clear problem description:
select a.projectid, a.assign_date, r.review_date, r.progress_note
from assignment a
join review r on a.assignid = r.assignid
where not exists(select * from review r2
where r.assignid = r2.assignid
and (r.review_date > r2.review_date
or (r.review_date = r2.review_date
and r.reviewid > r2.reviewid))
I assumed that "the latest review" would be the one with the latest
reviewdate, and if there were two reviews on the same date, then you
wanted the one with the highest reviewid. If one field would be enough
to decide this, you could delete everything after 'or'.
If you want to change to also returning assignments that haven't been
reviewed yet, you can just change 'join' to 'left join'.
Alternatively, you could use:
select a.projectid, a.assign_date, r.review_date, r.progress_note
from assignment a
join review r on a.assignid = r.assignid
left join review r2 onr.assignid = r2.assignid
and (r.review_date > r2.review_date
or (r.review_date = r2.review_date
and r.reviewid > r2.reviewid))
where r2.reviewid is null
(these two different options are just alternatives and ought to have the
same performance, use the one you think is the easiest to understand).
HTH,
Set
