Re: How To Exclude True Values

--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> > Richard, that is the result i would need given
> that
> > data set.  i have to digest this version, though.
> > 
> > should this query be more efficient than the
> subquery
> > version as the table starts to get large?
> 
> My experience is that Distinct On queries do not
> preform as well as their group by counter parts. 
> I believe that others have also commented to the
> same effect.
> 
> To speed the query you could apply indexes on the
> group by fields and the join columns.
> 
> Also, I produced a second query using PostgreSQL:
> 
> select a.id_i, a.ir_id, a.test, a.stamp
> from test a 
> 
> join
> (
> select max(stamp) as mstamp, id_i
> from test
> group by id_i
> ) b
> 
> on a.stamp = b.mstamp
> 
> where a.test = false
> ;
> 
> -- result
> 
>  id_i | ir_id | test |        stamp
> ------+-------+------+---------------------
>     4 |     8 | f    | 2006-06-05 08:00:00

Richard,

given the following table structure...

t_inspect
=========
inspect_id
...

t_inspect_result
================
inspect_result_id
inspect_id (fkey)
inspect_result_pass (bool) -- indicates fail or pass
inspect_result_timestamp

the following query...

select a.inspect_id, a.inspect_result_id,       a.inspect_result_pass,       a.inspect_result_timestamp
from t_inspect_result a 

join
(
select max(t_inspect_result.inspect_result_timestamp)       as mstamp,       t_inspect_result.inspect_id
from t_inspect_result
group by t_inspect_result.inspect_id
) b

on a.inspect_result_timestamp = b.mstamp

yields the following error:

ERROR:  schema "a" does not exist

i tried to interpret you query and apply it to my
case, but, apparently, i didn't too good of a job.

do you see the error?

tia...

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