On Jan 24, 2011, at 10:50 AM, Fredric Fredricson wrote:
> I have been fighting with a select and can find no satisfactory solution.
>
> Simplified version of the problem:
>
> A table that, in reality, log state changes to an object (represented as a row in another table):
>
> CREATE TABLE t (
> id SERIAL UNIQUE,
> ref INTEGER, -- Reference to a row in another table
> someData TEXT,
> inserted DATE DEFAULT CURRENT_TIMESTAMP
> ) ;
> Then we insert multiple rows for each "ref" with different "someData".
>
>
> Now I want the latest "someData" for each "ref" like:
>
> ref | someData (only latest inserted)
> -------------
> 1 | 'data1'
> 2 | 'data2'
> etc...
>
> The best solution I could find depended on the fact that serial is higher for higher dates. I do not like that
becauseif that is true, it is an indirect way to get the data and could possibly, in the future, yield the wrong result
ifunrelated 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 ;
>
> Can somebody come up with a better solution? (without resorting to stored procedures and other performance killers).
I would argue that relying on the id is safer than relying on the current timestamp because CURRENT_TIMESTAMP refers to
thetime that the transaction is started, not when the transaction was committed (or the row was "actually" inserted).
Inaddition, it is technically possible for two transactions to get the same CURRENT_TIMESTAMP. SERIAL values are never
reused.You could also create a security view which exposes the historical data but without the primary key in the
actualtable.
I recommend http://pgfoundry.org/projects/tablelog which uses "performance killers" like stored procedures to handle
thingsproperly- at least take a look to see how things are handled.
Cheers,
M