Date: Thu, 19 Feb 2004 15:52:27 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Edmund Bacon <ebacon@onesystem.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help refining/eliminating recursive selects
In-Reply-To: <1077232156.23327.10.camel@elb_lx.onesystem.ca>
Message-ID: <20040219154507.B63910@megazone.bigpanda.com>
References: <1077232156.23327.10.camel@elb_lx.onesystem.ca>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 19 Feb 2004, Edmund Bacon wrote:

>
> I have the following table:
>
> create table test (
>     id        serial primary key,
>     product   integer,
>     tx_date   date,
>     quantity  integer)
>
> with the following data:
>  id | product |  tx_date   | quantity
> ----+---------+------------+----------
>   1 |       1 | 2004-01-01 |       10
>   2 |       2 | 2004-01-01 |        8
>   3 |       3 | 2004-01-01 |        7
>   4 |       4 | 2004-01-01 |       12
>   5 |       1 | 2004-01-15 |        9
>   6 |       2 | 2004-01-15 |       12
>   7 |       3 | 2004-01-15 |        8
>   8 |       5 | 2004-01-07 |       15
>
>
> what I want to do is to find the most recent record for each product in
> the table.

If you don't mind using a PostgreSQL extension, I think distinct on
might help you.

Maybe something like the following:
 select distinct on (product) * from test order by product desc, tx_date
desc;

This might be helped by an index on (product, tx_date).