> > 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
