On 01/24/2011 04:56 PM, Tom Lane wrote:
Fredric Fredricson<Fredric.Fredricson@xxxxxxxxxxxxx> writes:
... Now I want the latest "someData" for each "ref" like:
The best solution I could find depended on the fact that serial is
higher for higher dates. I do not like that because if that is true, it
is an indirect way to get the data and could possibly, in the future,
yield the wrong result if unrelated changes where made or id's reused.
Here is my solution (that depend on the SERIAL):
SELECT x.ref,x.someData
FROM t as x
NATURAL JOIN (SELECT ref,max(id) AS id FROM t GROUP BY ref ORDER BY
ref) AS y ;
Well, you could just substitute max(inserted) for max(id). But you
I tried this, but that did not get me "someData" because I need "id" for
that.
should also consider using DISTINCT ON --- look at the "weather reports"
example in the SELECT reference page.
DISTINCT ON did the trick. Thank you!
My select is now much simpler:
SELECT DISTINCT ON (ref) ref, someData FROM t ORDER BY red,date DESC;
Also 20-30% faster in my setup.
I tried DISTINCT but I wasn't aware of the "DISTINCT ON" functionality.
You live - you learn.
BTW, "inserted DATE DEFAULT CURRENT_TIMESTAMP" looks pretty fishy.
You sure the column type shouldn't be timestamp or timestamptz, to
support multiple updates per day?
Sorry, my typo, it is really a TIMESTAMP(0), of course.
/Fredric
regards, tom lane
begin:vcard
fn:Fredric Fredricson
n:Fredricson;Fredric
org:Ln4 Solutions AB
email;internet:Fredric.Fredricson@xxxxxxxxxxxxx
title:CTO
tel;home:+46 8 91 64 39
tel;cell:+46 70 677 58 48
version:2.1
end:vcard
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
