From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: inner join and limit |
Date: | 2010-06-03 16:08:45 |
Message-ID: | puwrug6quq.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
In article <4BFD5BC0(dot)90900(at)unipex(dot)it>,
Michele Petrazzo - Unipex <michele(dot)petrazzo(at)unipex(dot)it> writes:
> Hi list,
> I have two table that are so represented:
> t1:
> id int primary key
> ... other
> t2:
> id int primary key
> t1id int fk(t1.id)
> somedate date
> ... other
> data t1:
> 1 | abcde
> 2 | fghi
> data t2:
> 1 | 1 | 2010-05-23
> 2 | 1 | 2010-05-24
> 3 | 1 | 2010-05-25
> 4 | 2 | 2010-05-22
> 5 | 2 | 2010-05-26
> I'm trying to create a query where the data replied are:
> join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with
> date order (of t2).
If you also want LIMIT N, the easiest way is probably the use of a
window function (PostgreSQL >= 8.4):
SELECT i1, i2, somedate
FROM (
SELECT t1.id AS i1, t2.id AS i2, t2.somedate,
rank() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC)
FROM t1
JOIN t2 ON t2.t1id = t1.id
) dummy
WHERE rank <= $N
From | Date | Subject | |
---|---|---|---|
Next Message | Wes James | 2010-06-04 02:16:46 | sum an alias |
Previous Message | Justin Graf | 2010-06-02 21:45:16 | Re: how to construct sql |