AnsweredAssumed Answered

SQL statement to return latest variable "Description" for all drawing files

Question asked by Benjamin Abshire on Apr 25, 2011
Latest reply on Nov 30, 2011 by Kent Keller

Can someone please help me past this hurdle. I am trying to write a simple SQL Select statement that will return the latest description variable for all the drawings in my vault. The closest I have gotten is this:

 

Select VariableValue.RevisionNo, VariableValue.ValueText, Documents.Filename
From VariableValue Inner Join
  Documents On Documents.DocumentID = VariableValue.DocumentID
Where Documents.Filename = '101-A20020.slddrw' And VariableValue.ConfigurationID
  = 5 And VariableValue.VariableID = 44
Order By Documents.Filename, VariableValue.RevisionNo Desc

 

Note: ConfiguraionID 5 = "@" and VariableID 44 = "Description"

 

The above statement returns this list:

RevisionNo___ValueText____Filename

10__________Test Desc5___101-A20020.slddrw

9___________Test Desc4___101-A20020.slddrw

8___________Test Desc3___101-A20020.slddrw

7___________Test Desc2___101-A20020.slddrw

6___________Test Desc1___101-A20020.slddrw

 

I only need the top line to be returned. The only method I've found that comes close is to insert TOP 1 after Select. But of corse, TOP 1 does not work if I want a list of all documents (i.e. change the FileName to: Like '*.slddrw'.

 

Thanks

Outcomes