Multiple group by problem [closed]

Question

I need to find the name of the common records which have similar fields in them. For Example - I will create a vf page to dynamically take 3 fields for an object.

And I need an Soql Query to search for duplicate records in all the 3 fields together and must retrn the names of records which have the common (Same) values for all those fields. Note - I need to get only the name of the records. Please help. My query is - " This is a Dynamic query "

 string SoqlStr=' SELECT ' + Field1 + ' , ' + Field2 + ' , ' + Field3 + ' , ' + ' COUNT(ID) ' +  
          ' FROM Account GROUP BY ' + Field1 + ' , ' + Field2 + ' , ' + Field3 + 
          ' HAVING COUNT(Field1) >= 1 AND COUNT(Field2) >= 1 AND COUNT(Field3) >= 1 ';

This is my basic built query . Please help me to transform it. The object is a standard object.

Answer 1

Your query isn't valid because it includes Field1, Field2, and Field3 literally, instead of doing string concatenation. That's one reason I encourage using String.format() to template dynamic queries like this one:

String query = String.format(
    'SELECT {0}, {1}, {2}, count(Id) FROM Account GROUP BY {0}, {1}, {2} HAVING count({0}) >= 1 AND count({1}) >= 1 AND count({2}) >= 1',
    new List<String> { 'Name', 'Industry', 'AccountSite' } // or whatever fields you pick
);

Your HAVING() clauses are, I think, misplaced here. What you really want is duplicate groups, so you need HAVING count(Id) > 1. Your current HAVING() clauses are tautological because any group will have a count of values in the grouped fields above or equal to one.

One of the limitations of aggregate queries in Salesforce is that you cannot extract the field values for individual grouped rows. This query will return AggregateResult objects that include the Id count and the values of the three fields that define the group. You would then need to perform an additional query for each group of duplicate records to identify the names of the records in that group, unless Name is one of the grouped fields. Then and only then can you include Name in your SELECT clause.

That said, I think this is a risky route to handle duplicate records. In a large database, this query will require careful tuning to avoid timing out, and it may well time out anyway. I'd encourage using Salesforce's native duplicate management rules.