6 Replies Latest reply on Jul 1, 2018 3:08 PM by Ctm Pdm Admin

    SQL query most recent distinct values

    Ctm Pdm Admin

      Hi everyone,

       

      I need to get a list of the 10 most recent distinct values entered in our PDM database for "Approved by Eng" variable.

       

      This is the standard query that gets all distinct values for that variable but I don't know how to sort them based on most recent entries. (I know I can use TOP 10 or LIMIT 10 to truncate the list, but the list has to be sorted based on most recent first)

       

      Select distinct Vv.ValueCache

      From VariableValue Vv

      Where Vv.VariableID =  (     select variableid

                          from variable

                          where Variablename like 'Approved by Eng')

             And Vv.ValueCache != ''

      order by Vv.ValueCache ASC

       

      Does anyone know how to get the most recent entries? I'm SQL-illiterate.

        • Re: SQL query most recent distinct values
          Joy Garon

          Without knowing exactly what you want, try this:

           

          Select distinct TOP 10(Vv.ValueCache)

          From VariableValue Vv

          Where Vv.VariableID =  (     select variableid

                              from variable

                              where Variablename like 'Approved by Eng')

                 And Vv.ValueCache != ''

          order by Vv.ValueCache DESC

            • Re: SQL query most recent distinct values
              Ctm Pdm Admin

              Thanks for the reply.

               

              Let me clarify (or maybe confuse), I want to sort the table based on Modified Date/Time, get a list of distinct values for column "Approved by Eng" then truncate that list to the top 10.

               

              Document     Approved By Eng     Date Modified     Doc. Version (=kinda irrelevant)

              File1               Eng1                         06/20/2018          6

              File1               Eng2                         06/21/2018          8

              File2               Eng7                         05/01/2018          3

              File5               Eng9                         06/29/2018          22

              File3               Eng8                         06/28/2018          170

              File2               Eng8                         05/02/2018          4

              File9               Eng2                         06/28/2018          4

               

              I want a list of Approved by Eng sorted by most recent, let's say top 4 distinct values of that list would be:

               

              Eng9

              Eng8

              Eng2

              Eng1

               

              If there was a column like Vv.DateModified then I assume this query would have worked:

               

              Select distinct TOP 10 Vv.ValueCache, Vv.DateModified

              From VariableValue Vv

              Where Vv.VariableID =  (     select variableid

                                  from variable

                                  where Variablename like 'Approved by Eng')

                     And Vv.ValueCache != ''

              order by Vv.DateModified DESC

               

              But I don't know if such column exists in the table or not.

            • Re: SQL query most recent distinct values
              Ctm Pdm Admin

              Finally figured it out....

               

              SELECT TOP 10 ValueCache,  MAX(Date) AS DATE

              INTO #tmp

              FROM VariableValue JOIN Revisions ON (VariableValue.DocumentID = Revisions.DocumentID AND VariableValue.RevisionNo = Revisions.RevNr)

              WHERE VariableID =  ( SELECT VariableID  FROM Variable  WHERE VariableName LIKE 'Approved by Eng')  AND ValueCache != ''

              GROUP BY ValueCache ORDER BY MAX(Date) DESC   

              SELECT     ValueCache  FROM #tmp

               

              Some details:

              1. Date Modified for each file is in the "Revisions" table in the database. So I had to JOIN the "Revisions" table and "VariableValue" table based on DocumentID and version numbers (RevNr and RevisionNo columns) to find the date for each 'Approved by Eng' entry in the database.
              2. Also getting the values based on the aggregate function MAX(Date) and Grouping them by name is the key here. This basically selects the most recent entry for the similar values. Then sorting them by MAX(date) to get a chronological list.
              3. Couldn't use DISTINCT since it messes everything up by re-ordering everything based on the first column alphabetically.
              4. To get rid of the MAX(Date) column at the end I had to put everything into a temporarily table and then select ValueCache column only. Using the 2-columned query (without the #tmp table) creates the list but the values in the list include both columns and Search Cards couldn't find any results since the value they searched for was like "Jon Doe 6/29/2018 8:40:46 PM" instead of just "Jon Doe".
              5. Read somewhere that JOIN is faster than WHERE clause so you may want to write the query as below to speed up if the database is huge:

               

              SELECT TOP 10 VV.ValueCache,  MAX(Date) AS DATE

              INTO #tmp

              FROM VariableValue AS VV JOIN Revisions ON (VV.DocumentID = Revisions.DocumentID AND VV.RevisionNo = Revisions.RevNr)

              JOIN Variable AS V ON V.VariableID = VV.VariableID AND VariableName LIKE 'Approved by Eng'

              WHERE VV.ValueCache != ''      

              GROUP BY ValueCache ORDER BY MAX(Date) DESC   

              SELECT ValueCache FROM #tmp

               

              Peace