> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
