Re: limiting join results

On Thu, 14 Nov 2002, Elaine Lindelef wrote:

> I am doing a query with a 3-way join. The join and select are working
> fine. However, what I want is to select only the row with the
> smallest timediff for each distinct t1.date.
>
> This is the query (simplified):
>
> select t1.date, t1.parent,
> t1.id, t2.id, t3.id, t3.date,
> (t3.date - t1.date) as timediff
>   from (t1 LEFT JOIN t2
> ON t1.parent = t2.id)
> LEFT JOIN t3 ON t2.page = t3.page
> where
> t3.date < t1.date and
> t3.event_type = 'page' and
> t1.user_id = '61516' and
> order by t1.date, timediff;

If you don't mind a postgres specific solution,
I think
select distinct on (t1.date) t1.date, ...

may give you what you want.

A real SQL solution is a bit more involved, I think you need
to do a subselect with a group by.