> > an inspection node (each row in t_inspect is an > > inspection node) that has passed can't have a new > > defect added - since it has already passed. > > > > therefore, in the defect entry form, i only want > to > > display those inspection nodes that don't have a > true > > value. by definition, a true value will always be > the > > last (by time) inspect_result. > > > > therefore, i need all the inspect nodes that don't > yet > > have a true value (iow, a true value in the last > (by > > time) inspect_result_pass row). > > > > an inspection node can have multiple inspection > > results, hence, the t_inspection_results table. > > > > this might seem counter-intuitive at first, but it > > makes sense since it may take 5 tries to > eventually > > pass a particular inspection node (f, f, f, f, t) > for > > fucntional test, for example. one node, five > tests to > > pass it. > > here is a test I did. bye the way, I did this is > access as it is the only source available to me > at the moment. > > table = test > id_i ir_id test stamp > 1 1 No 6/5/2006 1:00:00 AM > 1 2 No 6/5/2006 2:00:00 AM > 1 3 Yes 6/5/2006 4:00:00 AM > 2 4 Yes 6/5/2006 4:00:00 AM > 3 5 No 6/5/2006 5:00:00 AM > 3 6 Yes 6/5/2006 6:00:00 AM > 4 7 No 6/5/2006 7:00:00 AM > 4 8 No 6/5/2006 8:00:00 AM > > > query -- > > SELECT a.id_i, a.ir_id, a.test, max(a.stamp) as > mstamp -- PostgreSQL might not require an > aggregate with the group by. > > FROM test a INNER JOIN > ( > SELECT Max(stamp) AS tmax, id_i > FROM test > group by id_i > ) b > > ON a.stamp = b.tmax > > group by a.id_i, a.test, a.ir_id, a.stamp > > having a.test = false > > ORDER BY a.id_i,a.ir_id, a.test > > ; > > results -- > > id_i ir_id test mstamp > 4 8 No 6/5/2006 8:00:00 AM
Richard, that is the result i would need given that data set. i have to digest this version, though. should this query be more efficient than the subquery version as the table starts to get large? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
