Please don't be scared by the size of this question. I'm sure it will be quite easy to solve.

I have two tables:

[Id] [Name] [Contents*]

[Id*] [Content] [TimeStamp]

The Contents field of the Pages table relates to the Id field of the Contents table (asterix).

On any normal occasion I would simply do this to join the tables together:
SELECT Pages.Name, Contents.Content FROM Pages INNER JOIN Contents ON Pages.Contents=Contents.Id

However, there may be two rows in the Contents table with the same ID, just a newer timestamp. This is to keep a history of previous changes to the contents.

So I would like to join Pages.Contents to Contents.Id but only if Contents.TimeStamp is newer than all the other rows with the same ID.

I hope you understand.


[Id] [Name] [Contents]
1 Page1 1
2 Page2 2

[Id*] [Content] [TimeStamp]
1 Lalala 01/04/07
2 Stuff 02/04/07
2 Otherstuff 2 07/04/07

I want to get the name of page with Id 2 with it's latest contents.
Therefore it should return:
Page 2 and Otherstuff

How can I do this? Thanks a lot.