Re: SQL Question - S/B Good...

> > On Thu, May 25, 2006 at 13:56:09 -0700,
> > operationsengineer1(at)yahoo(dot)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.
>
> thanks for the lead

Bruno,

This looks like it does what i want...

SELECT DISTINCT ON (t_inspect_result.inspect_id)
t_inspect_result.inspect_id,
t_inspect_result.inspect_result,
t_inspect_result.inspect_timestamp
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'
ORDER BY t_inspect_result.inspect_id DESC,
t_inspect_result.inspect_timestamp DESC
-- inspect_id desc impacts end result. time desc
-- impacts the groups prior to being distinctly listed

as you can see in the note, the timestamp DESC works
within the groups, not on the single group value
ultimately returned (as it should). how can i get it
to work on returned groups, too? i work around it by
putting inspect_id in desc order (they typically occur
one after another), but that may not always work.

tia...

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