Re: limiting join results

On Friday 15 November 2002 12:14 am, 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

You try cursor
begin;
declare cursor c1 for select ....;
fetch forward 2 from c1;
commit;

regards
Haris Peco