Order Field by List in SOQL SELECT

Question

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?

Answer 1

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.

Answer 2

You can use Order By in SOQL as well, and you can have Order By multiple fields.

list<Contact> cons = [Select Id, Name From Contact Order By LastName, FirstName, CreatedDate];

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_orderby.htm