From: | "Luigi Antognini" <luigi370(at)produs(dot)ch> |
---|---|
To: | "'Michele Petrazzo - Unipex'" <michele(dot)petrazzo(at)unipex(dot)it>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: inner join and limit |
Date: | 2010-05-26 20:13:36 |
Message-ID: | D8359135792A485BA28720B9BE7643AA@produs.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello
Here a suggestion for your problem.
SELECT a.id AS t1_id, d.id AS t2_id, d.somedate AS t2_somedate
FROM t1 a
JOIN
(
SELECT id, t1id, somedate
FROM t2 b
WHERE (t1id, somedate) IN
(
SELECT t1id, somedate
FROM t2 c
WHERE c.t1id = b.t1id
ORDER BY somedate DESC
LIMIT 1
)
) d
ON (a.id=d.t1id);
t1_id | t2_id | t2_somedate
-------+-------+-------------
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26
(2 rows)
Hope this helps
Regards (Saluti da Zurigo)
Luigi Antognini
-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 7:35 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] inner join and limit
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).
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?
Thanks,
Michele
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Landscheidt | 2010-05-26 21:03:49 | Re: inner join and limit |
Previous Message | Rolando Edwards | 2010-05-26 19:04:19 | Re: inner join and limit |