limiting join results

From: Elaine Lindelef <eel(at)cognitivity(dot)com>
To: PostgreSQL General List <pgsql-general(at)postgresql(dot)org>
Subject: limiting join results
Date: 2002-11-15 00:14:00
Message-ID: v04210109b9f9e66fb35e@[172.16.2.101]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Wolfe 2002-11-15 00:33:35 Re: [GENERAL] Upgrade to dual processor machine?
Previous Message Josh Berkus 2002-11-14 23:37:28 Re: [PERFORM] Upgrade to dual processor machine?