Hi

This is a total SQL question but as it is relating to the EPDM database I am starting my search for help with this post.

I have an INNER JOIN on the “VariableValue” and “DocumentsInProjects” tables to get all of the document in a project.

OK to this point

I now have a list of all the Variable Value that are linked to a document ID with each variable having its own ID?

The problem that I face is that if VariableID 100 is the client address and that value has been change I have 2 instances of VariableID one with a RevisionNo value on 1 and one with a RevisionNo value of 2.

The big Q

How do I get only the VariableValue relating to the last revision?

THANKS

SELECT [VariableID]

,[VariableValue].[DocumentID]

,[VariableValue].[ProjectID]

      ,[RevisionNo]

,[ConfigurationID]

      ,[ValueText]

      ,[ValueInt]

      ,[ValueFloat]

      ,[ValueDate]

      ,[ValueCache]

      ,[IsLongText]

  FROM [PDMWE_BluewaterDMS].[dbo].[VariableValue]

  INNER JOIN [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects]

  ON [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[DocumentID]=[PDMWE_BluewaterDMS].[dbo].[VariableValue].[DocumentID]

  where [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[ProjectID]='895'

  and [PDMWE_BluewaterDMS].[dbo].[DocumentsInProjects].[Deleted]='0'