0
select Id, Name from sumchans__AddressReportMATeam__c where Name in (select Name from sumchans__AddressReportMATeam__c group by Name LIMIT 3)

I am not sure how to explain what I am trying to get here. In the above query I want all records from table based on the unique records from a column in the same table. Is this something that can be achieved in SOQL? The table that we are accessing has a column varied count of unique records in a column.

enter image description here Plz advise.

  • Are you trying to obtain the count of records for each value in the Name field? I'm not sure I follow. – David Reed Jan 27 at 19:30
  • @David Thats almost right, but not the count of records but the actual records of values in Name field. I got the original post edited with the example table, In that image the unit count varies for each building. I am trying to get the records of each building for example here the buildings would be 10 highwater path & 248 Hanover st in one single soql call. – Sumchans Jan 27 at 19:40
  • So you just want the unique values of the Name field across all records? – David Reed Jan 27 at 19:56
  • @David, in the above soql the inner would get us 2 records - 10 highwater path & 248 hanover st based on the above table. 10 highwaterpath has 8 records and 248 hanover st has 4 records. The outer soql should get us all the records for each record that was output by the inner query. – Sumchans Jan 27 at 20:03
  • How is that different from querying the whole table? Do you want to output only the rows that have a Name value that is shared with other records? – David Reed Jan 27 at 20:05
1

If it was plain sql you could use something like DENSE_RANK()

However or unfortunately this is not an option, perhaps doing something like an aggregate group by might do the trick for you?

SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName

The above group me 76000 opportunities into unique names: enter image description here

This option is not ideal as you will be breaking best practices by doing queries inside a loop, however, as i mentioned the funtioned that you could use is not available in soql (I added the link as it will be nice for you to be familiar with it)

The aggregate example that i gave you will help you find unique names, then you can do something like below:

AggregateResult[] groupedResults = [SELECT StageName, COUNT(Id) FROM Opportunity GROUP BY StageName];
System.debug(groupedResults.size());

List<sObject> result = new List<sObject>();
//I GUESS YOU CAN DECIDE THE NUMBER
if (groupedResults.size() <= 50) {
    for (AggregateResult ar : groupedResults)  {
        String soql = 'SELECT Id, Name FROM Opportunity WHERE StageName = \'' + ar.get('Name') + '\' LIMIT 1';
        System.debug(soql);
        sObject singleResult = new sObject();
        singleResult = Database.quer(soql);
        result.add(singleResult);
    }
}

Now if the result of the aggregate is too big (number of unique), you will definitely not be able to do this. I would do, (quick solution) is creating a custom object and save the records in there (When all conditions are met) perhaps I will add in there a unique field constrain, and I will query directly this object.

  • I am not looking for the count, but actual records, as each record has varied column information. – Sumchans Jan 27 at 20:50
  • I just edit and add to my answer a more complete solution that hopefully help you visualize a possible solution by using this aggregate query – manza Jan 27 at 22:01
  • Did it help the answer? will be interesting to know how you end up doing it – manza Jan 29 at 22:38
  • I used yours, it worked, then went with a more leaner version which was Davids.. Thanks. – Sumchans Jan 29 at 22:47
  • Cool, i am glad. Can I ask with the solution of @david wouldnt you get more than 1 record with the same name? perhaps I misunderstood the question, but is always nice to learn other approaches :) – manza Jan 29 at 23:11
1

You can't end-run around the transaction SOQL rows limit this way.

Essentially, you have two choices here:

One, you can use a SOQL Aggregate Query, which will yield you fields that are grouped and aggregated only - not row-level details. When you run an aggregate query, you're charged against your 50,000 row limit only the count of aggregate line items that are returned.

To make this concrete, you can do

select count(), Name from sumchans__AddressReportMATeam__c GROUP BY Name

to get back a List<AggregateResult> containing the count of records sharing each Name value. You'd be charged SOQL rows against your limit equal to the number of aggregate line items returned, i.e., the number of unique Name values. You cannot get back details on the rows that are aggregated when you use an aggregate query.

The other option is to filter your query to reduce the total number of rows returned, without using aggregate functions. That's the only way you'll get row-level detail (rather than aggregate detail), but the downside is that you are charged every single row returned against your 50,000 row limit.

Again, to make this concrete, you could filter your query by doing something like this:

SELECT Id, Name FROM sumchans__AddressReportMATeam__c where Name in ('Name', 'Other Name', 'A third name')

If what you really want is to find all of the records whose Name value is shared with some other number of records, you'll have to run two separate queries: one aggregate and one not:

Set<String> names = new Set<String>();
List<AggregateResult> ars = [SELECT Name FROM sumchans__AddressReportMATeam__c GROUP BY Name HAVING count(Id) > 3];
for (AggregateResult ar : ars) {
    names.add(ar.get('expr0'));
}

List<sumchans__AddressReportMATeam__c> records;
records = [SELECT Id, Name FROM sumchans__AddressReportMATeam__c where Name in :names];

That will get your the line-level detail of records whose Name is shared with a total of more than 3 records. It'll cost you one row per Name that matches those conditions (for the aggregate query) plus one row per record having that Name (from the non-aggregate query).

  • Thanks @David. very nicely explained, actually I was having questions about the limits as well, which I was reading through. just wondering if I could group by 3 fields in the first soql and in the second soql retrieve those records with multiple where in fields. – Sumchans Jan 28 at 2:13
  • @Sumchans I think you are overcomplicating this, and we might be getting into x-y problem territory. – David Reed Jan 28 at 2:20
  • Yeah I knew that, I am sorry, just want the code to be foolproof. – Sumchans Jan 28 at 2:22
  • Totally understood. Happy to support that objective! In general, you'll find that you can't gimmick the limits (there are a few exceptions). It's better to structure your code differently to work within them, even when that may force painful changes in the approach. Feel free to ask more questions to refine this issue, but just keep in mind that folks are having a hard time understanding exactly what you're trying to do here. – David Reed Jan 28 at 2:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.