2

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?

2
  • May i know where you are writing this query?
    – Subhash
    Jun 16, 2016 at 8:39
  • @Subhash I am testing it in Workbench. link
    – grass
    Jun 16, 2016 at 8:42

2 Answers 2

2

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.

0

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]; 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .