0

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?

1

To directly answer your question, no its generally not possible to get the database to return the results the way you are expecting.

The Good

The closest you can get is:

SELECT Type, MIN(CreatedDate) minCreatedDate
FROM Opportunity
GROUP BY Type
ORDER BY CreatedDate ASC

This will return:

Type                CreatedDate
Existing Business   23-12-2015
New Business        14-01-2016

The bad

Technically, this query will work:

SELECT Id, Type, MIN(CreatedDate) minCreatedDate
FROM Opportunity
GROUP BY Id, Type
ORDER BY CreatedDate ASC

Grouping by an Id in this instance is effectively grouping by 1 record. When you do this you will get 1 row per record, the type of that row and the created date of that row. This is the exact same as the following query:

SELECT Id, Type, CreatedDate
FROM Opportunity

Both of these queries will return:

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

It's important to understand that the point of grouping and aggregating is to summarize data in chunks. Id by its very nature is unique and as such will never be able to chunk out the data appropriately.

The Crazy

One might even be tempted to try something like this:

SELECT MIN(Id), Type, MIN(CreatedDate) FROM Opportunity GROUP BY Type

However, there is no guarantee that MIN(Id) will actually return the first created Id of a given type. When I ran this query I got 1994 as one of my dates but my ID was for an opp created in 2019.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.