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