Re: limiting join results

Elaine,

Are you sure of the where clause? (t3.date - t1.date) should be negative
if t3.date < t1.date!

JLL

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;
>
> Here are my results:
>
> t1.date | parent | t1.id | t2.id | t3.id |
> t3.date | timediff
> ------------------------+--------+--------+--------+--------+---------
> ---------------+----------
> 2002-11-14 14:46:33-08 | 102846 | 100918 | 102846 | 102845 |
> 2002-11-14 14:46:11-08 | 00:00:22
> 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102855 |
> 2002-11-14 15:33:50-08 | 00:00:11
> 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102853 |
> 2002-11-14 15:33:40-08 | 00:00:21
> 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102847 |
> 2002-11-14 14:46:35-08 | 00:47:26
> 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102845 |
> 2002-11-14 14:46:11-08 | 00:47:50
> (5 rows)
>
> What I want are only the first two rows. However, I don't know how
> many distinct t1.date values I will have. Using DISTINCT doesn't seem
> to change the output, and I'm not convinced it would keep the correct
> row if it did.
>
> My normal habit is to clean up the results in perl, but it seems to
> me that I should be able to do it in the SQL query and be a bit
> cleaner.
>
> Thank you for your assistance.
>
> Elaine Lindelef
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org