Michele Petrazzo - Unipex <[email protected]> wrote: > 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). > Data should be: > t1.id | t2.id | t2,somedate > 1 | 3 | 2010-05-25 > 2 | 5 | 2010-05-26 > As said, I'm trying, but without success... > Can be done for you? In addition to Luigi's and Rolando's responses, there are of course the always glorious "DISTINCT ON" for the "LIMIT 1" case: | SELECT DISTINCT ON (t1.id) t1.id, t2.id, t2.somedate FROM t1 JOIN t2 ON t1.id = t2.t1id ORDER BY t1.id, somedate DESC; and window functions for the generic one: | SELECT t1_id, t2_id, t2_somedate FROM | (SELECT t1.id AS t1_id, | t2.id AS t2_id, | t2.somedate AS t2_somedate, | ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC) AS rn | FROM t1 JOIN t2 ON t1.id = t2.t1id) AS SubQuery | WHERE rn <= 2; Tim -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
