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).
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
