Limit in WHERE-Clause subquery

Question

I want to do a query like this:

SELECT Id, Name, Email 
FROM User 
WHERE Id IN (
    SELECT OwnerId 
    FROM Opportunity
    WHERE AccountId = '001610000085XsNAAU'
    ORDER BY LastActivityDate DESC NULLS LAST
    LIMIT 1
)

But I have an error:

expecting a right parentheses, found 'ORDER'

Isn't it possible to use ORDER and LIMIT in subqueries?

Answer 1

The short answer is: No, you can't use ORDER and LIMIT in semi-join subqueries:

COUNT, FOR UPDATE, ORDER BY, and LIMIT aren’t supported in semi-join subqueries.

The purpose of semi-join queries is to restrict the records returned not selecting related records, thus using ORDER BY or LIMIT makes no sense in this case.

Answer 2

Try to write the query in 3 steps such as:

List<Opportunity> opps = [SELECT id,OwnerId FROM Opportunity WHERE AccountId = '001610000085XsNAAU' ORDER BY LastActivityDate DESC NULLS LAST Limit 1];

Set<Id> ids= new Set<id>();
for(Opportunity o :opps){
    ids.add(o.ownerId);
}

List<User> users = [SELECT Id, Name, Email FROM User WHERE Id IN : ids];