Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
connect(): No such file or directory
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: inner join and limit
Date: Wed, 26 May 2010 21:03:49 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 53
Message-ID: <m3iq6aqssq.fsf@passepartout.tim-landscheidt.de>
References: <4BFD5BC0.90900@unipex.it>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: g224125170.adsl.alicedsl.de
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:3mwPGhfFZ7IGhatGnEJg6PL6VE8=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.51 tagged_above=-5 required=5 tests=BAYES_05=-0.5,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/117
X-Sequence-Number: 34165

Michele Petrazzo - Unipex <michele.petrazzo@unipex.it> 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