pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting latest record
5+ messages / 4 participants
[nested] [flat]
* selecting latest record
@ 2009-09-22 09:44 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 2 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2009-09-22 09:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a simple table
price(id_product, price, date)
which records price changes for each id_product. Each time a price
changes a new tuple is created.
What is the best way to select only the latest price of each id_product?
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: selecting latest record
@ 2009-09-22 09:56 Pavel Stehule <pavel.stehule@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2009-09-22 09:56 UTC (permalink / raw)
To: pgsql-sql
Hello
2009/9/22 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> Hi,
>
> I have a simple table
>
> price(id_product, price, date)
>
> which records price changes for each id_product. Each time a price
> changes a new tuple is created.
>
> What is the best way to select only the latest price of each id_product?
there are more ways - depends on what you wont.
one way is
SELECT *
FROM price
WHERE (id_product, date) = (SELECT id_product, max(date)
FROM price
GROUP BY
id_product)
Regards
Pavel Stehule
>
> Thanks,
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: selecting latest record
@ 2009-09-22 10:02 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2009-09-22 10:02 UTC (permalink / raw)
To: pgsql-sql
On Tue, Sep 22, 2009 at 11:56:54AM +0200, Pavel Stehule wrote:
>
> there are more ways - depends on what you wont.
>
> one way is
>
> SELECT *
> FROM price
> WHERE (id_product, date) = (SELECT id_product, max(date)
> FROM price
> GROUP BY
> id_product)
Nice.
I didn't know one could have several args in a single WHERE clause.
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: selecting latest record
@ 2009-09-22 10:08 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: A. Kretschmer @ 2009-09-22 10:08 UTC (permalink / raw)
To: pgsql-sql
In response to Louis-David Mitterrand :
> Hi,
>
> I have a simple table
>
> price(id_product, price, date)
>
> which records price changes for each id_product. Each time a price
> changes a new tuple is created.
>
> What is the best way to select only the latest price of each id_product?
There are several ways to do that, for instance with DISTINCT ON (only
postgresql):
test=*# select * from price ;
id_product | price | datum
------------+-------+------------
1 | 10 | 2009-09-01
1 | 12 | 2009-09-10
2 | 11 | 2009-09-10
2 | 8 | 2009-09-13
(4 rows)
test=*# select distinct on (id_product) id_product, price from price order by id_product, datum desc;
id_product | price
------------+-------
1 | 12
2 | 8
(2 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: selecting latest record
@ 2009-09-22 14:09 Rob Sargent <robjsargent@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2009-09-22 14:09 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
Let's say there's an index on the date column: Does the where clause
approach necessarily out perform the distinct on version? Hoping the OP
has enough data to make analyse useful.
A. Kretschmer wrote:
> In response to Louis-David Mitterrand :
>
>> Hi,
>>
>> I have a simple table
>>
>> price(id_product, price, date)
>>
>> which records price changes for each id_product. Each time a price
>> changes a new tuple is created.
>>
>> What is the best way to select only the latest price of each id_product?
>>
>
> There are several ways to do that, for instance with DISTINCT ON (only
> postgresql):
>
> test=*# select * from price ;
> id_product | price | datum
> ------------+-------+------------
> 1 | 10 | 2009-09-01
> 1 | 12 | 2009-09-10
> 2 | 11 | 2009-09-10
> 2 | 8 | 2009-09-13
> (4 rows)
>
> test=*# select distinct on (id_product) id_product, price from price order by id_product, datum desc;
> id_product | price
> ------------+-------
> 1 | 12
> 2 | 8
> (2 rows)
>
> Andreas
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-09-22 14:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-22 09:44 selecting latest record Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2009-09-22 09:56 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-09-22 10:02 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2009-09-22 10:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-09-22 14:09 ` Rob Sargent <robjsargent@gmail.com>
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