Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id DDF476322F0
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 22 Sep 2009 06:57:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 19892-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 22 Sep 2009 09:56:51 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yw0-f179.google.com (mail-yw0-f179.google.com
	[209.85.211.179])
	by mail.postgresql.org (Postfix) with ESMTP id D644A6321A8
	for <pgsql-sql@postgresql.org>; Tue, 22 Sep 2009 06:56:56 -0300 (ADT)
Received: by ywh9 with SMTP id 9so4849640ywh.19
	for <pgsql-sql@postgresql.org>; Tue, 22 Sep 2009 02:56:55 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:content-type;
	bh=SDk2LvVY+thsS8gRhU6ORl2WOb48siB1m4UxuJq655U=;
	b=GxpddCRf93Agyruqc9YgR922qECy+6pXLBjpu8HU5eQFnqDz+5udQd6osyIjt+6h5G
	dyyJfGBqZrJLxPD4PA/E/qiXUWs2GcwcZ+WaejhnHHw5K8piVK2r/G4bK83ZanftKWwP
	/qL05qS+qpaEj3Ac0y5H63wAkL/gkM3+uTqoY=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type;
	b=cJT2C7QvYe15rGMaCPx88QjE9uiBui92NFKc7g2oJba0adXXrwvbLEYGlCKuY6zWU+
	JXbS/G7flDrpB85twxIg87BnvITpXglTxrLFvjDAuHmuOihbRc5VXNxumkDkXj1RDro3
	4GH8EGfu+BX/gZW8xcDiwoUBBI8Dp1Fd21gvs=
MIME-Version: 1.0
Received: by 10.150.240.4 with SMTP id n4mr1521249ybh.1.1253613415525;
 Tue, 22
	Sep 2009 02:56:55 -0700 (PDT)
In-Reply-To: <20090922094457.GA24885@apartia.fr>
References: <20090922094457.GA24885@apartia.fr>
Date: Tue, 22 Sep 2009 11:56:54 +0200
Message-ID: <162867790909220256k3865e0dcr742163d7828573c8@mail.gmail.com>
Subject: Re: selecting latest record
From: Pavel Stehule <pavel.stehule@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.576 tagged_above=-10 required=5 tests=AWL=0.023,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200909/101
X-Sequence-Number: 33166

Hello

2009/9/22 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> Hi,
>
> I have a simple table
>
> price(id_product, price, date)
>
> which records price changes for each id_product. Each time a price
> changes a new tuple is created.
>
> What is the best way to select only the latest price of each id_product?

there are more ways - depends on what you wont.

one way is

SELECT *
   FROM price
  WHERE (id_product, date) = (SELECT id_product, max(date)
                                                               FROM price
                                                              GROUP BY
id_product)

Regards
Pavel Stehule

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