I've got a postgres database having a user table (userid, firstname, lastname) along with a usermetadata table (userid, code, content, produced datetime). I store various details about each user within the usermetadata table by code and a complete history. so for instance, a person (userid 15) has got the following metadata:
15, 'QHS', '20', '2008-08-24 13:36:33.465567-04'
15, 'QHE', '8', '2008-08-24 12:07:08.660519-04'
15, 'QHS', '21', '2008-08-24 09:44:44.39354-04'
15, 'QHE', '10', '2008-08-24 08:47:57.672058-04'
I have to fetch a listing of my customers and the newest worth of all of various usermetadata codes. Used to do this programmatically also it was, obviously godawful slow. The very best I possibly could figure to get it done in SQL ended up being to join sub-chooses, that have been also slow and that i needed to do one for every code.
This really is really not too difficult to do in PostgreSQL since it has got the "DISTINCT ON" clause in the Choose syntax (DISTINCT ON is not standard SQL).
SELECT DISTINCT ON (code) code, content, createtime FROM metatable WHERE userid = 15 ORDER BY code, createtime DESC;
Which will limit the came back leads to the very first result per unique code, and when you sort the outcomes through the create time climbing down, you will get the latest of every.
I guess you are reluctant to change your schema, so I am afraid my answe is probably not of great importance and help, but here goes...
One possible solution is always to possess the time area empty until it had been changed with a more recent value, whenever you place the 'deprecation date' rather. One other way would be to expand the table by having an 'active' column, but that will introduce some redundancy.
The classic solution is always to have both 'Valid-From' and 'Valid-To' fields in which the 'Valid-To' fields are blank until another entry becomes valid. This is often handled easily by utilizing triggers or similar. Using constraints to make certain there's just one item of every type that's valid will make sure data integrity.
Present with these is the fact that there's just one method of identifying the group of current fields. You'd simply choose all records using the active user along with a NULL 'Valid-To' or 'deprecation date' or perhaps a true 'active'.
You may be thinking about considering the Wikipedia entry on temporal databases and also the article A consensus glossary of temporal database concepts.
A subselect may be the standard method of carrying this out kind of factor. You simply need a distinctive Constraint on UserId, Code, and Date - and you can run the next:
SELECT * FROM Table JOIN ( SELECT UserId, Code, MAX(Date) as LastDate FROM Table GROUP BY UserId, Code ) as Latest ON Table.UserId = Latest.UserId AND Table.Code = Latest.Code AND Table.Date = Latest.Date WHERE UserId = @userId