Re: How To Exclude True Values - Mailing list pgsql-sql

> > > how can i exclude true values for this query?
> > > 
> > > http://www.rafb.net/paste/results/obtkGz26.html
> > > 
> > > if i uncomment out 
> > > 
> > > --    AND t_inspect_result.inspect_result_pass =
> 'f'
> > > 
> > > it looks for prior falses within an inspect_id
> and
> > > returns it.  i want the original result set
> minus
> > > the
> > > trues, if possible.
> > > 
> > > tia...
> > 
> > this SQL appears to do the trick...
> > 
> > http://www.rafb.net/paste/results/zZKIjH80.html
> 
> I have one idea that hopefully wont complicate you
> query but it could simplfy your query by
> getting rid of the query nexting.  Also,  I haven't
> tested it.
> 
> Basically,  Replace the 
> DISTINCT ON (t_inspect.inspect_id) 
> 
> construct with 
> 
> GROUP BY t_inspect.inspect_id
> HAVING t_inspect_result.inspect_result_pass = 'f'

Richard, thanks for taking the time to review the
query.  i think this is what you meant...

http://www.rafb.net/paste/results/Wzxbxw69.html

do correct it if i misunderstood or if you see
something that will get me the intended result.

this result doesn't give me what i want.

here are the tables...

t_inspect
inspect_id
...

t_inspect_result
inspect_id (fkey to t_inspect)
inspect_result_pass (bool)
inspect_result_timestamp
...

here's some sample data...

inspect_id, inspect_result_id, inspect_result_pass,
inspect_result_timestamp

1, 1, f, 2006-06-05 01:00:00
1, 2, f, 2006-06-05 02:00:00
1, 3, t, 2006-06-05 03:00:00

2, 4, t, 2006-06-05 04:00:00

3, 5, f, 2006-06-05 05:00:00
3, 6, t, 2006-06-05 06:00:00

4, 7, f, 2006-06-05 07:00:00
*4, 8, f, 2006-06-05 08:00:00*

the original query will return a single result - the
result within the "*" markers.

i wany all the inspect_ids where the last (time wise)
related inspect_result_pass is a false.  if the last
value isn't false, i don't want it at all.

the query linked in this post will return 3 lines...

1, 2, f
3, 5, f
*4, 8, f* -- the only one i really want

instead of totally excluding latest time
inspect_result_pass true values, grouped by inspect_id
(what i need), it checks for prior false values and,
if they exist, it returns the prior false value row.

if you know of a way to update the above linked query
to exclude those rows where the latest time value of
inspect_result_pass is pass, grouped by inpect_id, be
sure to let me know.

thanks again for taking the time to review the code
and provide input.

while i'm no sql expert, this particular query was the
most challenging one i've run into so far.

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