[SalesForce] Order Field by List in SOQL SELECT

I have an aggregate query on the Lead object. The query returns a list of Leads grouped by website (custom field) in a specific order based on a MAX of a date attribute.

Say once we iterate through the List, the ordered list of websites is ["websiteB", "websiteF", "websiteA"].

What if I want to re-query against the Lead object, and order the leads by this list of websites?

In mySQL, I could do an ORDER BY FIELD(…, …, ..), or perhaps a case-when (see here: https://stackoverflow.com/questions/1244028/sql-order-by-list-of-strings). Any ideas in SOQL?

Best Answer

In SOQL you need to add a formula field called e.g. WebsiteOrder__c to hold the case expression:

CASE(Website__c,
"websiteB", 100,
"websiteF", 200,
"websiteA", 300,
1000
)

and then just order by that field.

Related Topic