Thread: SQL Question
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
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.
> 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_number_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
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.
> 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
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.