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

There is no documentation I could find that specifies that LIMIT and/or ORDER BY do not work with subqueries, but I ran into the same error you mentioned.

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.

Here's an example:

-- Edit --

SELECT OwnerId, Owner.CustomField__c FROM Case WHERE CaseNumber LIKE '%1026' ORDER BY CreatedDate DESC LIMIT 1

Turns out Custom Fields are not accessible because OwnerId is a polymorphic key referencing either Group or User. That means the above won't work, sorry.

To work-around this is very complicated. You would have to create a custom lookup field called "User Owner", or something. That would store a lookup reference to the User, if the Owner is a User (this can be checked by comparing the beginning of OwnerId to '005', the User ID prefix). That field would need to be populated using a after insert, after update Trigger. All values for this new field would need to be dataloaded for previously existing Cases. But, once you have this new "User Owner" field, you can access custom fields on User through SOQL, using it.

Answer 2

ORDER BY and LIMIT don't make sense in your subquery because you're not returning records from the subquery. Instead, the subquery just builds a list of IDs used to filter the main query.

If you use a subquery in a way that the subquery records are returned, these clauses are fine. For example, this works:

SELECT Name, 
    (SELECT FirstName, LastName FROM Contacts ORDER BY LastName LIMIT 10) 
FROM Account