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
Date: Thu, 19 Feb 2004 15:52:27 -0800 (PST)
Message-ID: <20040219154507.B63910@megazone.bigpanda.com> (raw)
In-Reply-To: <1077232156.23327.10.camel@elb_lx.onesystem.ca>
References: <1077232156.23327.10.camel@elb_lx.onesystem.ca>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, ebacon@onesystem.com
Subject: Re: Help refining/eliminating recursive selects
In-Reply-To: <20040219154507.B63910@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox