Re: Help refining/eliminating recursive selects

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).