How to return last record in detail table for each record in master
table
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2016-03-05 02:30

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
The query should return assignid, assign_date, manager, Projectid, re
iewid, re iew_date and progress_note

----------------------------------------------------------------------
molly · 2016-03-05 02:42

Would something like this suffice to help you out ?

edit:
Since the above wanted to do some additional (strange) things, maybe
this is a better example with distinct on (but i have no idea if the
statement as showed there is supported)

edit2:
Following your description + above link i gave it a (hypothetical)
try:

master = assignment
- assignid
- asign_date
- manager
- projectid

detail = review
- reviewid
- assignid
- review_date
- progress_note

Wanted result:
m.assignid m.assign_date m.manager m.projectid d.reviewid
d.review_date d.progress_note


SELECT DISTINCT ON (m.assignid)
       m.*, d.reviewid, d.review_date, d.progress_note AS MyDetails
FROM   assignment m
LEFT   JOIN review d ON d.assignid = m.assignid
ORDER  BY m.assignid, d.review_date DESC;

----------------------------------------------------------------------
talorigomat · 2016-03-08 22:14

Hi Molly

Thanks for your suggestion.  It didn't quite do what I wanted.  I did
find an answer here: https://groups.yahoo.com/neo/groups/firebird-
support/conversations/messages/128467

NB: You need to change the '>' to '<' in the solution shown.