DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=x5nTAlJznhXpz1tA5/rmi2Y+BKF/DfAbyP+fmWPSFvGXXCy57R2vXxoNrodgdTnxXp1wkJnL+4eatvN33hMttrWfEQNECZ7icq1ae3ZT7ctoiP0eDQ3drfDwwvk5IfGLC5dqiMtj+X7cvjZxPJxOezID4scIGwen1JT1nD0mpGY=
	;
Message-ID: <20060606221337.4739.qmail@web31801.mail.mud.yahoo.com>
Date: Tue, 6 Jun 2006 15:13:37 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: How To Exclude True Values
To: operationsengineer1@yahoo.com,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20060606185639.4573.qmail@web33309.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> 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

Regards,

Richard Broersma Jr.