Fredric Fredricson <Fredric.Fredricson@bonetmail.com> 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
should also consider using DISTINCT ON --- look at the "weather reports"
example in the SELECT reference page.
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?
regards, tom lane