Subject Re: [firebird-support] Re: query to return the last entry in detail table for each entry in master table
Author liviuslivius
Post date 2016-03-07T10:21:50Z
Hi,
 
i suppose that you sholuld change
r.review_date > r2.review_date
 
to
 
r.review_date < r2.review_date
 
regards,
Karol Bieniaszewski
 
W dniu 2016-03-07 11:02:35 użytkownik talorigomat@... [firebird-support] <firebird-support@yahoogroups.com> napisał:
 
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.