From: Richard Broersma Jr <rabroersma@yahoo.com>
To: operationsengineer1@yahoo.com, "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: How To Exclude True Values
Date: Tue, 6 Jun 2006 15:13:37 -0700 (PDT)
Message-ID: <20060606221337.4739.qmail@web31801.mail.mud.yahoo.com> (raw)
In-Reply-To: <20060606185639.4573.qmail@web33309.mail.mud.yahoo.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com
Subject: Re: How To Exclude True Values
In-Reply-To: <20060606221337.4739.qmail@web31801.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox