Honestly, I have never understood how the GROUP keyword works, I actually read many articles in the salesforce site; however, I still cannot really use this keyword properly, especially when it comes to the combination of GROUP and ORDER keywords.
I found this article in StackOverflow which explains how to use "the ORDER BY and GROUP BY together"; however, I still don't understand the concept properly. For my taste, The custom object used in the StackOverflow article makes the article confusing.
Given the following Opportunity records:
Id Type CreatedDate
001 Existing Business 23-12-2018
002 Existing Business 21-12-2018
003 New Business 13-01-2017
004 New Business 14-01-2016
I would like to group the opportunities by Type, then from each group get the record that was created first.
Expected result:
Id Type CreatedDate
001 Existing Business 23-12-2015
004 New Business 14-01-2016
I am using the following query:
SELECT Type, Id FROM Opportunity GROUP BY Type ORDER BY CreatedDate ASC
Salesforce returns error message:
org.apache.cxf.binding.soap.SoapFault: MALFORMED_QUERY: Ordered field must be grouped or aggregated: CreatedDate
Is there a way to get the expected result with a single query? Can you also explain why your query works as I was 5?