From: "Luigi Antognini" <luigi370@produs.ch>
To: "'Michele Petrazzo - Unipex'" <michele.petrazzo@unipex.it>,
	<pgsql-sql@postgresql.org>
References: <4BFD5BC0.90900@unipex.it>
Subject: Re: inner join and limit
Date: Wed, 26 May 2010 22:13:36 +0200
Message-ID: <D8359135792A485BA28720B9BE7643AA@produs.ch>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-Reply-To: <4BFD5BC0.90900@unipex.it>
Thread-Index: Acr8+wx913vIs6ZpQXmzz+LG5ukq/AAE7GTA

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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 7:35 PM
To: pgsql-sql@postgresql.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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql