To: pgsql-sql@postgresql.org
connect(): No such file or directory
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: inner join and limit
Date: Thu, 03 Jun 2010 18:08:45 +0200
Lines: 41
Message-ID: <puwrug6quq.fsf@srv.protecting.net>
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: srv.protecting.net
X-Archive: encrypt
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:U7RXyx8e8GjO2PR3FXljNVvSuYY=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.011 tagged_above=-5 required=5
	tests=BAYES_40=-0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201006/16
X-Sequence-Number: 34207

In article <4BFD5BC0.90900@unipex.it>,
Michele Petrazzo - Unipex <michele.petrazzo@unipex.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