From: | Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | (Hopefully stupid) select question. |
Date: | 2011-01-24 15:50:33 |
Message-ID: | 4D3D9FC9.3010802@bonetmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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 ;
Can somebody come up with a better solution? (without resorting to
stored procedures and other performance killers).
/Fredric
Attachment | Content-Type | Size |
---|---|---|
Fredric_Fredricson.vcf | text/x-vcard | 207 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2011-01-24 15:55:32 | Re: error while trying to change the database encoding on a database |
Previous Message | Geoffrey Myers | 2011-01-24 15:43:37 | Re: searching for characters via the hexidecimal value |