From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: (Hopefully stupid) select question. |
Date: | 2011-01-24 15:56:35 |
Message-ID: | 7060.1295884595@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Geoffrey Myers | 2011-01-24 15:57:52 | Re: error while trying to change the database encoding on a database |
Previous Message | Adrian Klaver | 2011-01-24 15:55:32 | Re: error while trying to change the database encoding on a database |