--- 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
