7 Replies Latest reply on Nov 30, 2011 4:29 PM by Kent Keller

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

    Benjamin Abshire

      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