Re: SQL Question - Almost, Not Quite...

> 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