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=mP71g9dzy92TbomkgS/l1jPP7BzsGfMJ0WbVefWQ/TJMzkFP1v8EpD6WvchLCQbAg3PTtmG9g1BUBl+fbNimyUI8mU09bg10bKJWVh1DP8dYMy2gislAnweU6QuNZNjItnSwtDwqU1yMzBUk7YRZiUaX9nvdD+baSJs161+ZL6A=
	;
Message-ID: <20060606230141.3017.qmail@web33310.mail.mud.yahoo.com>
Date: Tue, 6 Jun 2006 16:01:41 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Re: How To Exclude True Values
To: Richard Broersma Jr <rabroersma@yahoo.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20060606221337.4739.qmail@web31801.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

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