I need a little help getting some better filtering on one of the views I'm trying to work with. I can't work out a clean way to put my data (or code) in here (control + K does a hyper link?) so my apologies in advance.
What I want to do is reduce the selection to only include the lines for document ID at the max revision for that document. So for example for documentID 117711 the max revision is 6 so the first two lines should be filtered out. I've tried the code below but it doesn't work because its filtering based on the max revision number of all the document IDs rather than looking specifically at the max revision number for each document. Is there anyway to do this?
My current code below. Any help is greatly appreciated!
SELECT * FROM VariableValue vv WHERE VariableID = 344 AND ConfigurationID =12 AND RevisionNo = (SELECT max(RevisionNo) From VariableValue WHERE VariableID = 344 AND ConfigurationID =12)]