Re: How To Exclude True Values

--- Richard Broersma Jr <rabroersma(at)yahoo(dot)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