From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Edmund Bacon <ebacon(at)onesystem(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Help refining/eliminating recursive selects |
Date: | 2004-02-19 23:52:27 |
Message-ID: | 20040219154507.B63910@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Yudie | 2004-02-20 00:16:31 | Re: Help refining/eliminating recursive selects |
Previous Message | Edmund Bacon | 2004-02-19 23:09:16 | Help refining/eliminating recursive selects |