Skip site navigation (1) Skip section navigation (2)

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

From: <operationsengineer1(at)yahoo(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL Question - Almost, Not Quite...
Date: 2006-05-25 21:30:05
Message-ID: 20060525213005.81453.qmail@web33308.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
> 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.  

thakns for the lead

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

In response to

Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2006-05-25 21:56:46
Subject: Re: SQL Question - Almost, Not Quite...
Previous:From: Bruno Wolff IIIDate: 2006-05-25 21:17:54
Subject: Re: SQL Question - Almost, Not Quite...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group