Subject Re: query to return the last entry in detail table for each entry in master table
Author
Post date 2016-03-07T10:02:35Z
Hi Set

Thanks for helping me with this.  Your answer has been a great help.  However there is a problem with the result set.  In the Assignment table I have the following data: (of interest is assignid 9 which is projectid 19)




ASSIGNID ASSIGN_DATE MANAGER PROJECTID
1 03/03/2016 11
2 03/03/2016 12
3 03/03/2016 13
4 03/03/2016 14
5 03/03/2016 15
6 03/03/2016 16
7 05/02/2012 17
8 05/02/2012 18
9 05/02/2012 19
10 03/03/2012 20

In the review table I have the following: (Note that assignid 9 has two entries.  One with a review date of 5/2/2012 and the other with a review date of 5/5/2015)

REVIEWID ASSIGNID REVIEW_DATE PROGRESS_NOTE
1 1 03/03/2016  
2 2 03/03/2016  
3 3 03/03/2016  
4 4 03/03/2016  
5 5 03/03/2016  
6 6 03/03/2016  
7 7 05/02/2012  
8 8 05/02/2012  
9 9 05/02/2012  
10 1 05/02/2012  
11 10 03/03/2012  
12 9 05/05/2015  

using your queries give me the following result:

PROJECTID ASSIGN_DATE REVIEW_DATE PROGRESS_NOTE
11 03/03/2016 05/02/2012
12 03/03/2016 03/03/2016
13 03/03/2016 03/03/2016
14 03/03/2016 03/03/2016
15 03/03/2016 03/03/2016
16 03/03/2016 03/03/2016
17 05/02/2012 05/02/2012
18 05/02/2012 05/02/2012
19 05/02/2012 05/02/2012
20 03/03/2012 03/03/2012

The result set has Projectid 19 (assignid 9) with the a review date of 05/02/2012 instead of 5/5/2015.  I'm not sure which bit of the query to tweak to get the expected result. Can I enlist your help again please.