i'm setting up a system that will record passes or
fails for a given inspection.

i have created a t_inspect_result table with three
columns (to keep it simple) - inspect_id (fk to
t_inspect.inspect_id), inspect_pass (boolean),
inspect_date.

to keep it simple, let's say i have qa (quality
assurance) and ft (functional test) inspections.

i need to generate a report that will let me know if a
given serial number has any inspections that have not
yet had a pass (still a failed unit for that test node
- qa or ft).

i must add that a unit can go through qa twice... for
example

qa test 1
fail
fail
pass

ft 1
pass

.... more work done ...

qa test 2
pass

given this structure, and hopefully i've communicated
it well enough, i'm concerned that it might be
difficult to weed out qa 1 fail followed by qa 2 pass.

that is...

qa test 1
fail

.... the bizrre happens ...

qa test 2
pass

how can i go about finding the qa test 1 failure?

if i did a inspect_date descending query with a limit
of 1, i'd get a pass and miss the failure.

i think it has to do with "group," but i'm not mind
gripping the concept.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Wed, May 24, 2006 at 15:06:53 -0700,
operationsengineer1@yahoo.com wrote:
>
> given this structure, and hopefully i've communicated
> it well enough, i'm concerned that it might be
> difficult to weed out qa 1 fail followed by qa 2 pass.
>
> that is...
>
> qa test 1
> fail
>
> ... the bizrre happens ...
>
> qa test 2
> pass
>
> how can i go about finding the qa test 1 failure?
>
> if i did a inspect_date descending query with a limit
> of 1, i'd get a pass and miss the failure.

Use "inspect_pass" in the WHERE clause.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> On Wed, May 24, 2006 at 15:06:53 -0700,
> operationsengineer1@yahoo.com wrote:
> >
> > given this structure, and hopefully i've
> communicated
> > it well enough, i'm concerned that it might be
> > difficult to weed out qa 1 fail followed by qa 2
> pass.
> >
> > that is...
> >
> > qa test 1
> > fail
> >
> > ... the bizrre happens ...
> >
> > qa test 2
> > pass
> >
> > how can i go about finding the qa test 1 failure?
> >
> > if i did a inspect_date descending query with a
> limit
> > of 1, i'd get a pass and miss the failure.
>
> Use "inspect_pass" in the WHERE clause.

Bruno, i don't understand how this would help my
situation, but i think the problem is that i poorly
communicated the original problem. ;-)

however, i am very close to what i want... but not
quite there.

i use this query...

SELECT t_inspect_result.inspect_result,
t_inspect.inspect_id
FROM t_inspect_result, t_inspect, t_inspect_area,
t_serial_number,
t_link_contract_number_job_number, t_job_number,
t_product
WHERE t_inspect.inspect_area_id =
t_inspect_area.inspect_area_id
AND t_inspect.serial_number_id =
t_serial_number.serial_number_id
AND t_serial_number.link_contract_number_job_number_id
=

t_link_contract_number_job_number.link_contract_nu mber_job_number_id
AND t_link_contract_number_job_number.job_number_id =
t_job_number.job_number_id
AND t_product.product_id =
t_job_number.product_id
AND t_inspect.inspect_id =
t_inspect_result.inspect_id
AND t_inspect.serial_number_id = '184'
GROUP BY t_inspect_result.inspect_timestamp,
t_inspect_result.inspect_result, t_inspect.inspect_id
ORDER BY t_inspect_result.inspect_timestamp desc

to get these results...

Row / inspect_result / inspect_id
1 / t / 107
2 / t / 106
3 / f / 100
4 / t / 100

i can't figure out how to group by
t_inspect.inspect_id and limit the query to one result
per inspect_id.

iow, i want to see...

Row / inspect_result / inspect_id
1 / t / 107
2 / t / 106
3 / f / 100

when i apply a limit 1, it returns a single result,
not a single result per inspect_id. i tried (), but
to no avail.

any guidance is, as always, much appreciated.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, May 25, 2006 at 13:56:09 -0700,
operationsengineer1@yahoo.com wrote:
>
> Row / inspect_result / inspect_id
> 1 / t / 107
> 2 / t / 106
> 3 / f / 100
> 4 / t / 100
>
> i can't figure out how to group by
> t_inspect.inspect_id and limit the query to one result
> per inspect_id.
>
> iow, i want to see...
>
> Row / inspect_result / inspect_id
> 1 / t / 107
> 2 / t / 106
> 3 / f / 100
>
> when i apply a limit 1, it returns a single result,
> not a single result per inspect_id. i tried (), but
> to no avail.

OK, I thought you were only querying one id at a time and the issue was
getting back an f avleu when there was a mixture of f and t values.
To get one value per inspect_id you can use the Postgres extension DISTINCT ON
to return one record per inspect_id. If you also ORDER BY inspect_id,
inspect_result then you should get a row with an f for inspect result if there
is one. Or if you only care about inspect_id's where there is at least one
row with an f, then you can add NOT inspect_id to the WHERE conditions.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> On Thu, May 25, 2006 at 13:56:09 -0700,
> operationsengineer1@yahoo.com wrote:
> >
> > Row / inspect_result / inspect_id
> > 1 / t / 107
> > 2 / t / 106
> > 3 / f / 100
> > 4 / t / 100
> >
> > i can't figure out how to group by
> > t_inspect.inspect_id and limit the query to one
> result
> > per inspect_id.
> >
> > iow, i want to see...
> >
> > Row / inspect_result / inspect_id
> > 1 / t / 107
> > 2 / t / 106
> > 3 / f / 100
> >
> > when i apply a limit 1, it returns a single
> result,
> > not a single result per inspect_id. i tried (),
> but
> > to no avail.
>
> OK, I thought you were only querying one id at a
> time and the issue was
> getting back an f avleu when there was a mixture of
> f and t values.
> To get one value per inspect_id you can use the
> Postgres extension DISTINCT ON
> to return one record per inspect_id. If you also
> ORDER BY inspect_id,
> inspect_result then you should get a row with an f
> for inspect result if there
> is one. Or if you only care about inspect_id's where
> there is at least one
> row with an f, then you can add NOT inspect_id to
> the WHERE conditions.

Bruno, no worries. i didn't explain what i wanted too
well. i'm *only* interested in the last (latest
timestamp) inspect_result for each inspect_id.

the reason this is important is to verify that a unit
has actually passed all prior inspection failures. if
the last (by timestamp) inspect_result is a fail, then
the unit is not to be shipped in its failure state.
if all the inspects are passes, the unit can ship.

it is a double check to close the inspection failure
loop.

i'll investigate distinct on and see where it leads
me.

thakns for the lead

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, May 25, 2006 at 14:30:05 -0700,
operationsengineer1@yahoo.com wrote:
>
> Bruno, no worries. i didn't explain what i wanted too
> well. i'm *only* interested in the last (latest
> timestamp) inspect_result for each inspect_id.

Then DISTINCT ON is the the simplest way to do this. You can ORDER BY
inspect_id DESC, timestamp DESC to get the last record for each inspect_id.
This is not standard SQL though.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend