Ordering and Limiting A Subquery In Salesforce SOQL

Question

I am attempting to retrieve the owner of a case, based on a partial match, where we choose the most recent case that matches the partial match.

This is the query I am attempting:

SELECT User.CustomField__c 

FROM User 

WHERE User.Id IN (

    SELECT OwnerId 

    FROM Case 

    WHERE Case.CaseNumber LIKE '%1026' 

    ORDER BY Case.CreatedDate DESC LIMIT 1)

The following query works on its own, but doesn't seem happy as part of the subquery:

SELECT OwnerId 

FROM Case 

WHERE Case.CaseNumber LIKE '%1026' 

ORDER BY Case.CreatedDate DESC LIMIT 1

Equally, if I drop the ORDER BY and LIMIT it works:

SELECT User.NVMContactWorld__NVM_Agent_Id__c 

FROM User 

WHERE User.Id IN (

    SELECT OwnerId FROM Case 

    WHERE Case.CaseNumber LIKE '%1026')

Are order / limit queries not allowed in a SOQL subquery?

Just to clarify this issue, the scenario I am dealing with looks like this...

A Salesforce organisation can configure the "display format" for Case Numbers. If they select "4" digits, you get case numbers like:

• 0001
• 0125
• 1234
• 33456

It is possible to reconfigure your case numbers to get the following case numbers as well as the case numbers above...

• 000001
• 001234
• 033456

I didn't want people to be confused by the LIKE statement, the issue is that 001234 and 1234 are different cases, so if a customer supplies 1234 and I find two records, I want to start off assuming that they are the most recent case.

So either consider the LIKE statement or an IN statement that contains ('001234', '1234')

Answer 1

However, it may work to start at the Case object and look up to the User, similar to the Lookup Relationships and Outer Joins section in the SOQL documentation. I'm not sure if this would work for you, but it's something you may want to try.

To work-around, this is very complex. You'll need to create a custom lookup field called "User Owner", or something. This will store a lookup reference to the User if the Owner is a User. This field will need to be populated using an after insert, after update Trigger. All values for this new field will need to be data loaded for earlier existing Cases. But, once you have this new "User Owner" field, you can access custom fields on User through SOQL, using it.

Be a certified Salesforce professional by going for Intellipaat’s Salesforce Certification!