+ Start a Discussion
Chris760Chris760 

SOQL Query for Newest Opportunity on Account

I'm trying to figure out how to write a SOQL query to display the newest opportunity associated with every account, using the MIN() SOQL aggregate function.  Unfortunately, I can't seem to wrap my head around how to structure this since aggregate queries only let you include the field that you're grouping by and the field you're aggregating [Example: AccountId, MIN(CreatedDate) from Opportunity], however I also need to know the Id of each resulting opportunity so that I can utilize it in the trigger.  How would I need to structure the query so that I can also extract the opportunity ID?  Thanks!

Best Answer chosen by Chris760
Chris760Chris760
Thanks Vamsi for the response.  The problem was that I needed to return a query that had the newest opportnity for a bunch of accounts, and by putting "LIMIT 1" on the query, I could only return 1 single record at a time, which was causing the trigger to hit the governor limit since I couldn't "bulkify" it.  I eventually figured out a way by just doing a basic parent-to-child relationships query: SELECT Id, Name, (SELECT Id, Name, CreatedDate FROM Opportunities ORDER BY CreatedDate DESC LIMIT 1) FROM Account.  Thanks for responding though!

All Answers

Vamsi KrishnaVamsi Krishna
Chris,
you can try this query

select id,name from opportunity
where accountid=:YourAccountId
order by createddate desc limit 1

replace YourAccountId with your actual account id..
Chris760Chris760
Thanks Vamsi for the response.  The problem was that I needed to return a query that had the newest opportnity for a bunch of accounts, and by putting "LIMIT 1" on the query, I could only return 1 single record at a time, which was causing the trigger to hit the governor limit since I couldn't "bulkify" it.  I eventually figured out a way by just doing a basic parent-to-child relationships query: SELECT Id, Name, (SELECT Id, Name, CreatedDate FROM Opportunities ORDER BY CreatedDate DESC LIMIT 1) FROM Account.  Thanks for responding though!
This was selected as the best answer