Getting an entry for every client that is not the latest in SOQL

Question

I have just created a query that gets the latest entry for every client in a custom object (see this question, with a great answer from @eyescream).

SELECT id,
       (SELECT score__c,
               date__c
        FROM my_custom_objects__r
        ORDER BY date__c DESC
        LIMIT 1)
FROM Client__c
WHERE id IN (SELECT client__c FROM my_custom_object__c)

I now need to create a query that selects the lowest score from the same object, but it can't be the latest entry, as I need to calculate the distance between lowest and latest. I know I can do this to get the lowest score:

SELECT id,
       (SELECT score__c,
               date__c
        FROM my_custom_objects__r
        ORDER BY score__c ASC
        LIMIT 1)
FROM Client__c
WHERE id IN (SELECT client__c FROM my_custom_object__c)

However in some cases the latest entry and the lowest score are the same entry, which I am trying to avoid. I have tried to get my head around how to get the lowest score that is not the latest entry. I tried using the first query in a "NOT IN" clause in the second, but I cannot get that to work.

Any help would be greatly appreciated.

score 0 · Answer 1 · answered Apr 20 '23 at 16:59

Query, collect the ids of records you don't want, send 2nd query?

Set<Id> idsToAvoid = new Set<Id>();

List<Client__c> clientsWithLatest = [SELECT id,
       (SELECT score__c, date__c
        FROM my_custom_objects__r
        ORDER BY date__c DESC
        LIMIT 1)
FROM Client__c
WHERE id IN (SELECT client__c FROM my_custom_object__c)];

for(Client__c c : clientsWithLatest){
    if(!c.my_custom_objects__r.isEmpty()){
        idsToAvoid.add(c.my_custom_objects__r[0].Id);
    }
}

Map<Id, Client__c> clientsWithLowest = new Map<Id, Client__c>([SELECT id,
       (SELECT score__c, date__c
        FROM my_custom_objects__r
        WHERE Id NOT IN :idsToAvoid
        ORDER BY score__c ASC
        LIMIT 1)
FROM Client__c
WHERE Id IN :clientsWithLatest]);

for(Client__c c : clientsWithLatest){
    Client__c lowest = clientsWithLowest.get(c.Id);
    System.debug(c.my_custom_objects__r + ' vs ' + lowest.my_custom_objects__r);
}

I had been wondering if that was the only way. Unfortunately APEX is not an option in this case (I should have mentioned that). However I have managed to set up a different solution with the help of flows and two custom fields. Thank you so much for you help, I do really appreciate it. — Kees, Apr 21 '23 at 09:45
Post your trick as another answer, maybe somebody will benefit from it in future? You'll still get reputation points for accepting the answer and I'd happily upvote too. — eyescream, Apr 21 '23 at 09:52

Getting an entry for every client that is not the latest in SOQL

1 Answers1