6 Replies Latest reply on May 18, 2015 8:24 PM by philmodjunk

    Finding duplicate records using ExecuteSQL

    Henry_2

      Title

      Finding duplicate records using ExecuteSQL

      Post

      Hi,

      I'm trying to find duplicate records using ExecuteSQL, but I can't seem to get the syntax right (my experience with these queries is still limited):

      tabel: media_assets
      fields: id, md5hash

      If the md5hash is equal, then it's a duplicate. My output should be a list of all id's that have a counterpart with the same md5hash value. Can anybody help me with proper ExecuteSQL syntax? I suspect it should be something like this:

      Let(
      [ $query = 
          "SELECT \"id\" 
          FROM media_assets 
          INNER JOIN ........ 
          ON ... .md5hash = .... .md5hash
       "
      ; $queryResult = $query // & $...
      ]; ExecuteSQL ( $queryResult ; "" ; "" )
      )

      fyi: I am aware that there are ways to find duplicates easily without ExecuteSQL, but in this case I'd like to solve it with an sql query to stay flexibile expanding the query for including other other (search) functionalities that can restrict the id's found.

      A step further would be to change the query so it will only output only the lowest id of each duplicate set found.

      Thanks,

      Henry