AnsweredAssumed Answered

Finding duplicate records using ExecuteSQL

Question asked by Henry_2 on May 15, 2015
Latest reply on May 18, 2015 by philmodjunk


Finding duplicate records using ExecuteSQL



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:

[ $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.